Tableau is one of the most powerful business intelligence tools on the market, their groundbreaking solutions help to present data in an informative way through charts and graphs. With its bestinclass functionalities, Tableau can handle data regardless of the level of complexity and transform intricate calculations into insightful visualisations. This is where we will encounter Level of Detail (LOD) calculations.
In this blog post, we will show you how you could use LOD calculations to express powerful concepts by using simple statements.
What is Level of Detail (LOD)?
Before we dive into LOD calculations, let’s take a quick look at what Level of Detail refers to. Let’s consider the following scenario to better understand level of details in views:
You are a sales manager looking to analyse the breakdown of sales by segments across the different regions. Your goal is to calculate the total sales amount per country and include it in the visualisation.
So, for this case, the base visualisation that we will build is to depict the sales by region (as shown below). The view is currently at the region level of detail as our rows shelf only has the dimension of region.
Now we want to add the dimension of segment to the visualisation to view the sum of sales by segment across the different regions. As we have added another dimension, our view now is at the region and segment level of detail.
As per the scenario, the next step would be to do a calculation to show the total sales amount per country and include this in our visualisation. So, how do we proceed when this new calculation is to be at the country level of detail, which is of a different level of detail than our view? This is where LOD calculations come in.
What are LOD Calculations?
LOD calculations were a breakthrough feature that was released in Tableau 9.0. LOD basically allows us to change the granularity of our analysis and enables flexibility in our visualisations, as we can explicitly define the level of detail we want to show in our views. With LOD, we can easily compute aggregations which might not necessarily be at the same level of detail as the visualisation.
In Tableau, there are three types of LOD calculations, FIXED, INCLUDE and EXCLUDE. In this blog post, we will be walking through examples of how these LOD calculations can be implemented in our views.
LOD Calculations: FIXED
FIXED LOD calculations aggregate the value only at the dimensions which are specified by the user in the calculation. For this LOD calculation, we do not take into account the dimensions which are already in the view. Let’s work on an example to see how we can implement FIXED LOD calculations. For this exercise, we will be using the Superstore dataset which is the sample data source that is provided in Tableau.
Let’s consider the following scenario:
You are a sales manager looking to analyse the breakdown of sales by subcategory across the different categories. Your goal is to calculate the percentage distribution of sales across the different subcategories. We want to find which subcategory contributed the most in terms of sales for the respective categories.
First, let’s build the base visualisation that we want to work with. In this case, we want to view the sales by subcategory across the different categories.
Now that we have the base visualisation, let’s take a look at how we can aggregate sales per category. We can do this by creating a calculated field with the FIXED LOD calculation.
The syntax for FIXED LOD calculations in Tableau is:
{[FIXED] <dimension>: <expression to aggregate>}
To meet the scenario’s requirements, our calculation should aim to return the sum of sales by category:
Now if we add this new calculated field to our view, we should be able to see the sales for each subcategory, as well as the total sales for each category.
With both streams of information in our view, we can now compute the percentage distribution of sales across the different subcategories for each category. We can create a new calculated field to do the following aggregation:
After adding the new measure to our view, we can now identify which subcategory contributed the most in terms of sales in each category. For example, chairs contributed to 44% of the total sales in the furniture category.
And there we have it, a view with different level of details which also shows aggregated values based on the dimensions we specify in our FIXED LOD calculations.
Additionally, since we are looking at percentage distribution, we can add subtotals to the view to ensure that the percentages add up to 100% to verify if our FIXED LOD calculation is correct.
LOD Calculations: INCLUDE
INCLUDE LOD calculations, compute aggregations while considering the dimensions which are specified in the calculation, as well as those dimensions which are already present in the view. INCLUDE LOD calculations will only result in measures and these calculations will be applied after the dimension filters. Let’s work on an example to see how we can implement INCLUDE LOD calculations. We will be using the same Superstore dataset for this exercise.
Let’s consider the following scenario:
You are a sales manager looking to compare the average customer sales across the different categories. Your goal is to find which category has the highest average sales per customer.
First, let’s build the base visualisation that we want to work with. In this case, we want to view the average sales by category.
Now that we have the base visualisation, let’s take a look at how we can aggregate average sales per customer across each category although our view does not contain any customer information. We can do this by creating a calculated field with the INCLUDE LOD calculation.
The syntax for INCLUDE LOD calculations in Tableau is:
{[INCLUDE] <dimension>: <expression to aggregate>}
To meet the scenario’s requirements, our calculation should aim to return the average sales by customer by including the customer information in our calculation:
Now if we add this new calculated field to our view, we should be able to see the average sales as well as the average customer sales across each category. With this view, we can now identify that technology had the highest average sales per customer.
And there we have it, a view with INCLUDE LOD calculation which includes a dimension (Customer Name) which was not present in the view.
Additionally, we can do the following to verify if our INCLUDE LOD calculation is correct. For this example, since we are looking at average customer sales by category, let’s add the total sales as well as the distinct count of customers to our view.
With the information in this view, let’s compute the average sales per customer in a new calculated field.
With this new calculated field, we can verify that Avg. Sales Per Customer field and Sales Per Customer field show the same values, which in turn ensures that our INCLUDE LOD calculation is accurate.
LOD Calculations: EXCLUDE
For EXCLUDE LOD calculations, we can omit specific dimensions which are already present in the view from our aggregations. EXCLUDE LOD calculations will only result in measures and these calculations will be applied after the dimension filters. Let’s work on an example to see how we can implement EXCLUDE LOD calculations. We will be using the same Superstore dataset for this exercise.
Let’s consider the following scenario:
You are a sales manager looking to compare the yearly sales across the different categories against the overall sales for each category. Your goal is to find the year which had the highest contribution to the overall sales for each category.
First, let’s build the base visualisation that we want to work with. In this case, we want to view the yearly sales by category.
Now that we have the base visualisation, let’s take a look at how we can aggregate total sales across each category although we have a yearly breakdown in our view. We can do this by creating a calculated field with the EXCLUDE LOD calculation.
The syntax for EXCLUDE LOD calculations in Tableau is:
{[EXCLUDE] <dimension>: <expression to aggregate>}
To meet the scenario’s requirements, our calculation should aim to return the total sales by category, while omitting the yearly information.
If we add this new calculated field to our view, we should be able to see the yearly sales as well as the total sales across each category.
With both sets of information in our view, we can now compute the percentage contribution of sales across the different year for each category. We can create a new calculated field to do the following aggregation.
After adding the new measure to our view, we can now identify which year contributed the most in terms of sales in each category. For example, the sales in 2022 contributed to 35% of the total sales in the office supplies category.
And there we have it, a view with EXCLUDE LOD calculation which excludes a dimension (Year of Order Date) which was already present in the view.
For this example, since we are looking at percentages, we can add subtotals to the view to ensure that the percentages add up to 100% to verify if our EXCLUDE LOD calculation is accurate.
When should we implement LOD Calculations?
We can implement LOD calculations when we want to compute values involving many dimensions, without bringing all of these dimensions into our view. LODs are also useful when we want to aggregate values outside of the visualisation’s level of detail.
They also help to deal with duplicate records which might be caused when we have tables with onetomany relationships. However, we can use the FIXED LOD calculation method to aggregate our values based on the dimensions we specify.
Another use for LOD calculations is when we want to isolate a value outside of the view we are building. For example, if we are looking to get a single aggregate of a value, the maximum sale price regardless of category or order date, LOD calculations come in handy as it can compute and retrieve the max sales value across all orders, regardless of the view we are building.
Tips & Tricks
Before building charts with the LOD calculations, it is recommended to build a table to confirm the calculations. From our example in INCLUDE LOD calculations, we aim to verify that the average sales per customer and sales per customer fields are showing the same values.
Once we have ensured that our calculations are correct, we can proceed to apply these LOD calculations to other charts.
Conclusion
In conclusion, Tableau has many powerful capabilities with one of the highlights being LOD calculations. With LOD calculations, we can compute values at different level of detail from the level of detail in the visualisations. There are mainly three types of LOD as shown below:
LOD Calculation  Description 
FIXED 

INCLUDE 

EXCLUDE 

With the examples provided, you are now equipped with the knowledge of how LOD calculations can be applied in our Tableau visualisations. You may wish to also take advantage of the free trial offered by Tableau to get some handson practice with LOD calculations.
Alternatively, with the assistance of Tableaucertified consultants from Billigence, we can further explore how Tableau solutions can benefit your organisation. Please feel free to submit the enquiry form below if you would like to get in touch and find out more about Tableau and its potential for your individual use case.