Using a row in a Datasource as a reference line

by Joe Beaven

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!

© 2022 The Information Lab Ltd. All rights reserved.