Some Datasources contain a row which is a natural reference line. An example of this came in my first weekly project last Friday where I looked at diversity in Tech companies. There was a row for each company, with column’s for company gender balance and proportion employees by race. There was also a row that showed the demographics of the US population, a natural reference point when considering representation. :
I wanted to build a view that showed each company compared to the US population shown as a reference line:
So to take a row in the dataset, build the following LOD:
This LOD calculation should be read inside out: First, the if statement which says, if the row (company in this case) is U.S. Population then show the [% Asian] for that category. This returns the % Asian for U.S. Population (null for everything else):
The fixed expression then returns the result for every row of the table:
Now we have the required field, we can start building up the view:
Set the LOD as a reference line, we need to average out as the required reference value is currently a field in all rows:
Thanks to Georgie, Hafeez and Jonathan for the help!