As part of my ongoing preparation for the Tableau Data Analyst Certification exam, I’ve been revisiting some of the most powerful features in Tableau, and one of them is Level of Detail (LOD) expressions. These notes are part of my personal study series, where I review key Tableau concepts that often appear in certification questions and, more importantly, in real-world analytics projects.
One of the most common challenges in Tableau is dealing with data that needs to be aggregated at different levels of detail. Sometimes, I need to calculate something at the customer level while visualizing data at the region level. Or, I might need to find the average sales per order but display it by category.
That’s where Level of Detail (LOD) expressions come in, one of the most powerful and flexible features in Tableau.
What are LOD Expressions?
LOD expressions allow you to control granularity of the calculations, independent of the visualization's level of detail.
Normally, Tableau aggregates your data based on the dimensions present in the view. But with LOD's, you can tell Tableau to perform calculations at a different level. In other words LOD expressions gives us the ability to compute values before or outside of the view's aggregation.
Syntax of LOD Expressions:
{ [LOD Type] [Dimension1], [Dimension2], ... : AGG([Measure]) }
There are three main types of LOD's -> FIXED, INCLUDE, EXCLUDE
- FIXED LOD
The Fixed LOD expression calculates a value string using dimensions you specify, regardless of what dimensions are in the view.
{ FIXED [Customer ID] : SUM([Sales]) }Calculates the total sales for each customer, even if the view is showing data by region, category, or month.
If you then create an average of this field:
AVG({FIXED [Customer ID] : SUM([Sales])})
you’ll get the average sales per customer, independent of your view’s granularity.
Use Case:
- Find the average sales per customer, while the view shows total sales by region.
- Calculate total orders per city, even if you’re visualizing by state.
- Lock calculations at a specific business level (account, store, or product).
- INCLUDE LOD
The INCLUDE LOD expression calculates a value including additional dimensions that may not be in the view. It effectively increases granularity.
{ INCLUDE [Order ID] : SUM([Sales])}
This expression calculates total sales per order, even if you’re currently visualizing at the category level.
If you then take the AVG of that expression, you’ll find:
AVG({INCLUDE [Order ID ] : SUM([Sales])})
the average sales per order, displayed at the category level
Use Case:
- Calculate average sales per order, while showing data by category.
- Compute customer-level metrics (like lifetime value) while viewing by region.
- Drill down temporarily into a finer level of detail.
- EXCLUDE LOD
The EXCLUDE LOD expression does the opposite of INCLUDE. It removes dimensions from the calculation, effectively decreasing granularity.
{EXCLUDE [Sub-Category] : SUM([Sales])}
This expression will calculate total sales without considering sub-categories, even if they are in the view.
So if your view shows Category -> Sub-Category -> Sales, the above expression will display category-level sales totals repeated across all sub-categories.
Use Case:
- Remove a lower-level detail to find higher-level averages.
- Compare detailed data to broader context (sub-category vs category total).
- Build visualizations like contribution ratios or % of total.
I hope this little preview of LOD expressions shines some light on how powerful they can be when working in Tableau. Understanding how to control the level of detail in your calculations opens the door to more accurate and flexible analysis.
As I continue my preparation for the Tableau Data Analyst Certification, exploring topics like this helps me not only review key exam concepts but also strengthen my real-world problem-solving skills. LODs are just the beginning. Next, I’ll dive into how they interact with filters and table calculations.