- Quick Note
- Fixed, Include, and Exclude OLDs
- Useful Resource
Welcome to the continuation of our series following the last blog post Unpacking LOD: The Basics. In this section, we are going to demonstrate how different types of LODs work in Tableau. We will explain how the viz is built and what each element is doing. You can follow along on your local machine with Tableau installed, the data set used in this exercise is called EU_Superstore_Orders and Superstore_Orders which can be downloaded from my GitHub page.
There are three types of LOD expression keywords—EXCLUDE, INCLUDE, and FIXED—each of which alters the scope of the LOD expression.
A key aspect of exploring data is understanding the structure of the source. For example, you may have restaurant inspection data that at the most granular level is listed by its street address. You may then want to aggregate the data to view properties by zip code, city, state, or even country.
In Tableau, you typically do this by dropping the dimensions you care about into your view (e.g., city, state). Depending on the dimensions you’ve chosen to add to the view, your data will be aggregated accordingly —to the “viz level of detail”, or Viz LOD for short.
So, the question we are looking to answer is the average sales per customer by state. Therefore, the dimension/viz level of detail should be [State]. Let’s drag the state onto the Rows. Because we are interested in the average sales PER CUSTOMER not PER STATE, it can be quite difficult to display that in Tableau without using a LOD calculation. Again, we know we can get the average sales per customer per state by getting sales per state and then dividing by the number of customers per state. For example, the average sales per customer in California is equal to 793, which is 457,688 (total sales in California) / 577 (total no. of customers in Cali). In order to achieve this we need a LOD calculation, as shown below.
Let’s create a new calculation field, this INCLUDE statement is used to force the calculation of sum of sales to be on the [Customer ID] level. Then we can drag this LOD into the Columns, it will show the average sales per customer (which is done in the LOD calculation) per state (which is our viz LOD or dimension).
Now let’s the sum of sales and distinct count of customer ID from the Columns, as we do not need them for the LOD to work, then we are done! (you can also drag the LOD calc onto the Colour Marks to give color intensity to the chart, so that your chart will look exactly the same!)
For this viz, we are interested in knowing what is the difference between the average profit per sub-category and average profit overall (or per row level, this is the total profit sum altogether and divided by the total number of records in the dataset). Again, a LOD comes in handy in this sort of situation. The EXCLUDE statement is used here to calculate the average profit per row level. First, let’s drag [sub-category] onto the Rows, and then we create a new calc field like the above.
Refer to the flow chart diagram for the EXCLUDE statement shown earlier, we specified the average profit calculation to not be aggregated on the sub-cat level, but this happens to be our viz dimension / LOD. According to the flow chart, this EXCLUDE statement will return the average calculation based on a more aggregated level. Because no other dimensions are being dragged onto the viz, so Tableau will return the required calculation based on the row level by default (which is the highest level of detail / the least aggregated). But if you have more than 1 dimension in the viz, for example, sub-cat and category on the Rows at the same time, and you used the same EXCLUDE statement then Tableau will look for the LOD that is more aggregated than sub-cat, in this case, would be a category.
The next step will be easy, as we only need to create another calc field that uses the average profit per sub-cat and subtract the average profit overall to get the difference we need to know.
Because the viz LOD is set to be sub-cat, the AVG([profit]) is going to return the average per sub-cat, and then our LOD calc is returning the overall average profit which is a constant number equal to $28.66.
For copiers, the difference between its average profit and overall average profit is $817.9-$28.66=$789.3
If you simply drag the second calc field onto the columns and also put that into the color marks on the left, you will have the exact same chart as me!
Now let’s use the EXCLUDE and INCLUDE OLD in the same viz, and compare their difference. Prior to making the viz, I created a hierarchy consisting of [Region], [State], and [City] in the Superstore_Order dataset. We are going to see how the viz changes as we switch between different dimensions.
After you create the hierarchy, drag it onto the Rows. Now put [Sales] onto the Columns, and aggregate it using SUM(). Then created two separate LOD calcs, so that one is INCLUDE [State] and the other is EXCLUDE [State] as shown above.
Now we have three different viz sharing the axis. Ask yourself the question “Can you tell what each of the viz is doing on [Region] level of detail?”
The answer is SUM([Sales]) is always returning the sum of sales at whatever the viz level is, so total sales per region in this case.
INCLUDE LOD is saying calculate the total sales at the [State] level, then consider the viz LOD. In this case, it is doing total sales per state, then per region, which does not make sense to us. Tableau will read this instruction and try to give us the calculation at the more aggregated LOD, which is [region]. So it is the same as SUM([Sales]).
EXCLUDE LOD is the same except we are telling Tableau to NOT do the total sales based on [State], then consider the viz LOD. That’s why they are all the same in this dimension.
Now we have two dimensions in the viz, Region and State. As usual SUM([Sales]) is doing what it is supposed to do. Because the viz LOD and our INLUCDE LOD calc are the same, so they return the same result, no surprise there. Our EXCLUDE LOD calc is saying to NOT aggregate the total sales at the state level, so it looks for a more aggregated LOD in the viz which is the region level.
(Note: if there are no other dimensions in the viz other than [State], then the EXCLUDE LOD will give the most aggregated calculations for SUM([Sales]), which is the sum of every records add together)
In this view, we have three dimensions.
SUM([sales]) is returning the total sales per city and all other LOD calcs are doing the same. The reason INCLUDE is doing this is because
The FIXED LOD is probably the easiest to understand because it is intuitive and straightforward. I am not going to repeat how to create this viz step by step as we already did the same thing earlier, but rather focus on explaining the FIXED LOD calc.
So, this LOD is saying we are summing the sales based on [Category] and ignoring whatever dimensions on the viz. Very easy to understand and extremely useful, which makes it the most common LOD used.
To do this we can use the sum of sales divided by the FIXED LOD calc we created earlier to get the percentage of the total. Don’t forget to format the calculation field to percentage.
LOD calculations in Tableau allow you to compute values at different LODs that are not included in your viz. It is very useful when answering questions such as “How do the Sales of all of my Categories compare to a selected Category?”, which would be quite difficult to achieve without LOD calc. Each LOD has a slightly different use case, but FIXED LOD is probably what you will use the most. The only thing worth noting is that FIXED LOD has a different order of operation in Tableau, which is why in the few rare cases you might need INCLUDE and EXCLUDE LODs over FIXED.
If you made it this far, I really hope you enjoyed it. Thanks for your time and I will see you next time!
Top 15 LOD Expressions. (n.d.). Tableau. https://www.tableau.com/blog/LOD-expressions
Narula, R. (2023, February 13). Granularity & Nested LOD Expressions — 3. Medium. https://email@example.com/granularity-nested-lod-expressions-3-d02140c28189#:~:text=In%20Tableau%2C%20granularity%20refers%20to
Tableau’s Order of Operations. (n.d.). Help.tableau.com. https://help.tableau.com/current/pro/desktop/en-us/order_of_operations.htm
Understanding Level of Detail (LOD) Expressions with Tableau. (n.d.). Www.tableau.com. Retrieved December 19, 2023, from https://www.tableau.com/learn/whitepapers/understanding-lod-expressions#form