I'm relatively new to Tableau and trying to replicate (or improve upon where possible) some benchmarking charts we used to do in Excel. Some sample data and an example chart is attached.
The challenge is to calculate rates (the example data is employment rates) for different overlapping groups higher education institutions, for example the London group, the England group, a list of comparator institutions and our own institution. We then want to display these different rates in a single chart, over time.
I've been trying to create calculated fields to calculate rates for each group, as below:
Home %: if attr([Institution_short])='Home' then sum([Number employed and/or studying])/sum([Base population]) else 0 end
Comparator %: if attr([Comparator])='Y' then [% in work or study] else 0 end
This calculates rates, but they show up as 0 unless I include institution name in the view. This means I cannot get the group rate, only the rates for the individual members in the group.
If anyone can help, or point me in the direction of the appropriate learning resources I would greatly appreciate it.