An Introduction to Tableau

An Introduction to LOD Calculations

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 best-in-class 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 

Dashboard

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.

Dashboard 2

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 sub-category across the different categories. Your goal is to calculate the percentage distribution of sales across the different sub-categories. We want to find which sub-category 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 sub-category across the different categories. 

Summary

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: 

Sales per category

Now if we add this new calculated field to our view, we should be able to see the sales for each sub-category, as well as the total sales for each category. 

Summary

With both streams of information in our view, we can now compute the percentage distribution of sales across the different sub-categories for each category. We can create a new calculated field to do the following aggregation: 

Sales per category

After adding the new measure to our view, we can now identify which sub-category contributed the most in terms of sales in each category. For example, chairs contributed to 44% of the total sales in the furniture category. 

Summary

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.  

Summary

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. 

Category vs Average Sales

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: 

Average sales per customer

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. 

Sales chart

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. 

Sales chart

With the information in this view, let’s compute the average sales per customer in a new calculated field. 

Sales per customer

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. 

Summary

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. 

Category chart

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.

Sales vs order date

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.  

Category summary

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. 

% sales per year

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. 

Category summary

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. 

Category summary

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 one-to-many 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. 

Summary chart

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 

  • Computes using dimensions specified in the calculation 
  • Does not consider dimensions in the view 

INCLUDE 

  • Computes using dimensions specified in the calculation 
  • Considers the dimensions in the view 

EXCLUDE 

  • Excludes the dimensions specified in the calculation 
  • Computes using dimensions specified in the view 

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 hands-on practice with LOD calculations. 

Alternatively, with the assistance of Tableau-certified 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.  

Leave a Comment

Scroll to Top

Discover the benefits of using Alteryx in Finance.

In this webinar you’ll learn about:

👍 Improving existing processes with Alteryx

👍 Data Formatting & Text Parsing use-case

👍 Reconciliation use-case

👍 Commonly used tools

👍 Inputs & outputs, macros, apps (and more!)

Days
Hours
Minutes
Seconds
Alteryx for Audit & Accounting