Continuing on my post on FIXED LODs, I wanted to shed some light on INCLUDE and EXCLUDE Level of Detail expressions. It’s an advanced topic and if you haven’t read my blog introducing LODs, make sure to do so before continuing with this post.
I’ll be using Sample-Superstore dataset available in Tableau for the purpose of this blog.
INCLUDE Level of Detail
The syntax for INCLUDE LOD is very similar compared to FIXED. The only difference is changing the keyword to ‘include’. The meaning, however, is quite different. You may recall that I used Coach Andy’s approach to translate FIXED LODs into plain English. I decided to apply the idea to INCLUDE LODs as well.
You can translate INCLUDE LOD as ‘For every dimension in the view AND every listed dimension, calculate the aggregate expression’:
For example, if we already have Sub-Category and Category in the view, the calculation will get the sum of Sales for each Sub-Category, Category and Product:
Hold on, but what happens now? We don’t have Product in the view, so what are we actually seeing?
Whenever you use INCLUDE LOD, you’re using more granular data than what you have in the view, so then this calculation needs to be aggregated to get back to the original (view) level of detail:
To make sure you use the correct aggregation for INCLUDE calculation, you can wrap the whole expression in the function of choice or right-click on the field pill -> Default Properties -> Aggregation -> [choose the aggregation here].
Use Case
Let’s say you want to know what are the average Sales by product within each Sub-Category. One way is to have Sub-Category, Product ID and Sales in the view creating a bar chart and then finding the average with a reference line:
It doesn’t look very nice, slightly too busy especially that we only want to see the averages, we don’t really care about particular product sales.
Another way is to use an INCLUDE calculation:
Apart from cleaning up the view, you can easily sort the data based on the average Sales.
You could argue that this can be also achieved with a FIXED LOD, which is true:
INCLUDE, however, adds more flexibility: you can add more dimensions in the view and values will change accordingly:
FIXED LOD would result in wrong values, unless you build a separate calculation for each of the options.
EXCLUDE Level of Detail
EXCLUDE LOD is pretty much the opposite of INCLUDE. Instead of adding more dimensions, you’re getting rid of them.
You can translate EXCLUDE LOD as ‘For every dimension in the view EXCEPT the listed dimension(s), calculate the aggregate expression’:
If we have Sub-Category and Category in the view, the sum of Sales will be calculated for each Category, disregarding the Sub-Category field:
This time the calculation is performed at a less granular level than what you have in the view and so the resulting values will be duplicated in the view:
Use Case
One thing you can use exclude for is to present detailed data together with less granular values for reference. For example, you can calculate the difference between average sales for Sub-Categories and average sales for the corresponding Category.
You’ll need one EXCLUDE LOD for this:
And a basic calculation using the above:
If you add Category, Sub-Category, Avg(Sales) and the two calculations above, this is what you get in your view:
Again, you could argue that instead of EXCLUDE you could use FIXED. This is true (again). With EXCLUDE (just like with INCLUDE), you can add more dimensions into the view and the values will be recalculated:
FIXED calculation
would have duplicated values across the segments:
To get exactly the same values, you’d need to create separate caluclations for each option.
Recap
To recap, the type of LOD (and whether you need an LOD calculation in the first place) will depend on what you have in the view. Another factor is whether you want your calculation to be flexible or not.
Apart from exams and exercises I haven’t used many INCLUDE or EXCLUDE LODs but it is definitely beneficial to understand what is going on.
I imagine the calculations are based on temporary tables that are changed whenever you use an LOD expression:
Whenever the calculation is performed for more granular data than the view, the values will be aggregated. When the calculation is performed for less granular data, the values will be duplicated:
This is quite a lot to handle, but I hope this post was helpful. There is still more to LODs. For example, FIXED is calculated before INCLUDE or EXCLUDE and so it will work differently with filters. To learn more on LODs and filters, check out Tableau help page. The recording of my webinar on LODs is up on YouTube if you want to check it out and the workbook I used for that webinar is on Tableau Public.