Introduction
Level of detail (LOD) expressions allows Tableau users to compute values both at data source level and visualization level - giving users more control on the level of granularity they can compute data.
In this article, I will be exploring the FIXED level of detail (LOD) expression.
FIXED level of detail (LOD) expression computes a value using the specified dimensions, without reference to dimensions in the view.
To demonstrate how to apply FIXED LOD, I have compiled these three examples.
Example 1: Computing percent of Sales generated from customers acquired in a certain period.
In this example, using the Sample – Superstore data set, let’s find out the percentage of sales generated in the year 2015 came from customers acquired in the year 2012?
To answer this question, first we need to compute the customer acquisition date. Date when each customer made their first purchase using the following FIXED LOD calculation.
(Note, this calculation returns each customer’s minimum Order Date - which is essentially the date of acquisition).
To answer the question.
Drag YEAR (Order Date) to the columns shelf.
Drag SUM (Sales) to the rows shelf.
Add table calculation Percent of Total computed down the table.
Drag YEAR (Customer acquisition date) to the color shelf.
(From this view, you can tell that, 72.72% of Sales generated in 2015 came from customers acquired in the year 2012)
Example 2: Computing Percent of total
Using Sample-Superstore dataset, compute the percentage of sales by State. Set this computation in such a way that when you filter the view by Region, the computations remain computed at country level.
Solution 1 - wrong approach
One would be tempted to answer this question by.
Adding State to the rows shelf.
Add Sales to the columns shelf.
Add table calculation Percent of Total computed down the table.
And labels to the view.
Although this appears to be the solution we’re looking for, notice when you filter the view by region - for instance ‘East’ the calculations are recomputed to return the proportion at regional level instead of national level – which is not what we’re looking for.
(Notice, New York value changes from 13.53% to 45.80% when you filter by East Region, our goal is to return the results computed at Country level when you filter by Region)
Solution 2 - correct approach (using FIXED LOD)
This can be corrected using FIXED LOD calculation as shown below.
(Notice in this case, I have replaced the denominator with a FIXED LOD calculation computing the total sales)
Now,
Add State to the rows shelf.
Add calculation ‘Percent of total’ to the columns shelf.
Format the calculation to percentage.
Notice when you filter the view by region, returns the results computed at national level (country level) – which is exactly what we’re looking for.
(Note, Filtering the view by East region doesn’t change the values which remain computed at national level e.g., the percent of sales in Ohio remains 3.41%)
Note, this behavior is because of the Tableau order of operations (the order in which Tableau operations are executed).
Where in this case, the FIXED LOD (applied as a denominator) is computed first before the dimension filter ‘Region’ is applied to the view – thus returning the percentages computed at national level.
Example 3: Proportional brushing
Proportional brushing is a technique of interacting with data in which, rather than filtering from a selection, you show the proportion of your selection in relation to all.
To demonstrate this, using the Sample–Superstore dataset, I’ll create a view showing Sales by product Sub_Category.
Drag Sub_Category to the rows shelf.
Drag Sales to the columns shelf.
Sort the view in descending order by sales.
Compute the Sales Amount again, now using a FIXED LOD Calculation as shown below.
Add the above calculation to the columns shelf.
Make the chart dual axis and synchronize the axis.
Ensure the LOD calculation ‘Sales Amount’ is at the back.
Add the dimension Region as a filter.
Notice when you filter the view by region, let’s say ‘West’ – the view shows the Sales generated by West region in relation to all regions (Proportional brushing).
Note, this behavior is because of the Tableau order of operations (the order in which Tableau operations are executed).
Where in this case, the FIXED LOD calculation ‘Sales Amount’ is computed first before the dimension filter ‘Region’ is applied to the view – therefore the chart at the back ‘Sales Amount’ remains computed at national level despite the regional filter (creating the brushing effect when you apply a filter).
Conclusion
I hope this article was helpful to you. To receive more of the Tableau tips, kindly join our mailing-list by subscribing below.
If you like the work we do and would like to work with us, drop us an email on our Contacts page and we’ll reach out!
Thank you for reading!
