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. :
![](https://www.thedataschool.co.uk/content/images/wordpress/2019/08/Data.png)
I wanted to build a view that showed each company compared to the US population shown as a reference line:
![](https://www.thedataschool.co.uk/content/images/wordpress/2019/08/Target-1-1024x413.png)
So to take a row in the dataset, build the following LOD:
![](https://www.thedataschool.co.uk/content/images/wordpress/2019/08/Calculation-1.png)
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):
![](https://www.thedataschool.co.uk/content/images/wordpress/2019/08/If-Table.png)
The fixed expression then returns the result for every row of the table:
![](https://www.thedataschool.co.uk/content/images/wordpress/2019/08/Fixed-Table.png)
Now we have the required field, we can start building up the view:
![](https://www.thedataschool.co.uk/content/images/wordpress/2019/08/View-1-1024x416.png)
Set the LOD as a reference line, we need to average out as the required reference value is currently a field in all rows:
![](https://www.thedataschool.co.uk/content/images/wordpress/2019/08/Ref-Line-Setup.png)
Thanks to Georgie, Hafeez and Jonathan for the help!