How to use level of detail (LOD) expressions in Tableau?

Reading time: 15 minutes

Level of difficulty: Difficult

Objective: Understanding LOD (Level Of Detail) calculations

‍

When working with Tableau, calculations are done at the view level. That makes sense because they match the information you're presenting. For example, if you analyze your data at the regional level, it is normal for your calculations, such as the sum of sales, to be done at the regional level.

‍

But what if the calculation you want to perform needs to be done at a different level of detail than the view?

Let's say you have a graph showing the sum of sales by region, but you also want to show, in the tooltip, the sum of sales by country to allow the user to compare regional performance to the national level. This is where LODs (Level of Detail) come in. They allow you to adjust the level of detail of a calculation, independently of that of the view.

‍

The 3 types of LOD calculations

1. FIXED : This type of calculation is ideal for setting a constant level of detail, regardless of the structure of the view. For example, if you want to calculate the sum of sales by country while analyzing sales by region, you would use a FIXED LOD to ensure that the calculation stays at the country level.

2. INCLUDE : This calculation allows you to add additional details to your aggregations without necessarily including them in the view. In practice, it allows you to take into account a specific field in your calculation, without making it visible in the visualization. This enriches your analysis while keeping the view simple and readable.

3. EXCLUDE : As the name suggests, this calculation excludes certain dimensions from your view. This is useful when you want to simplify the results by omitting specific information that could complicate the display, while maintaining most of the analysis.

‍

NB : INCLUDE and EXCLUDE rely on the view by adding or removing one or more levels of detail, while FIXED ignores the view level to perform the calculation.

Reminder : The level of detail of a view corresponds to the dimensions used in the view. If you are in doubt, simply ask yourself what dimensions would be present if your graph were in table form.

‍

On these images we have the same information in 2 different formats, we can see that the level of detail of the views is “Sub-Category”:

You may still find it all a bit abstract, and that's normal. With practice, these concepts will become clearer. In the following example, we'll show you step-by-step how to use these functions to adjust the level of detail in your calculations.

‍

Required Data

• 1 data set, for example: Orders (sample)-_superstore) _Orders.csv
• Several dimensions (Ex.: Country, Region, Product, Category...)
• 1 measure (like sales)

‍

Instructions

Step 1: Prepare your worksheet

1. Build a map by dragging the Country field into the tab Details of the landmark.
2. Select the type coordinate system Carte.
3. Drag the Region (group) field into the tab colour of the landmark.
4. Drag the State field into the tabs Details and Text of the landmark.
5. Drag the Sales field into the tab Infobulle of the landmark.
6. Look at the total sales in the tooltip, which is the sum by State.

‍

Step 2: LOD FIXED calculation

Objective: Display the sum of sales by Country, State, Region

1. Create a calculated field called “Country Level Sales.”
2. Type the following formula: {FIXED [Country]: SUM ([Sales])}.
3. Drag this field into the tooltip.
4. Repeat the process for the following calculated fields:
   • “Region Level Sales”: {FIXED [Region (group)]: SUM ([Sales])}
   • “State Level Sales”: {FIXED [State]: SUM ([Sales])}
5. Organize the tooltip for better readability.

‍

‍

Step 3: LOD EXCLUDE calculation

Objective: Exclude the State dimension from the sum of sales.

1. Create a calculated field called “Regional Sales - EXCLUDE.”
2. Use the following formula: {EXCLUDE [State]: SUM ([Sales])}.
3. Drag this field into the tooltip.

‍

In tabular format, we can see a little better what is going on:

‍

Step 4: LOD INCLUDE calculation

Objective: Show the average sales per customer (Customer ID) without adding this field to the view.

1. Create a calculated field called “Avg Customer Sales in State.”
2. Use the following formula: AVG ({INCLUDE [Customer ID]: SUM ([Sales])}).
3. Add this field to the tooltip.

How does it work?

• {INCLUDE [Customer ID]: SUM ([Sales])}: Here, we should imagine that we are adding the Customer ID dimension to our calculation, even if it is not present in the view. This means that at each level of the view (here State), Tableau takes into account the list of customers and calculates the sum of sales for each customer.
• AVG ({INCLUDE [Customer ID]: SUM ([Sales])}): As the level of detail in the view remains State, we need to specify how to aggregate this data. Using AVG, we get the average sales per customer for each state (State), which gives us a representative value at this level of detail.

‍

Conclusion

LOD calculations may seem complex at first, but they are very useful for comparing different levels of data. Take the time to fully understand the granularity of your view and start by organizing your data and doing your calculations in a table before turning them into a graph. This will allow you to clearly visualize what level of detail you are at so as not to get lost during the calculations.

‍

If you have any questions or specific needs, do not hesitate to contact us!