Introduction to LOD Expressions in Tableau
Learn how Level of Detail (LOD) Expressions allow you to control the granularity of calculations in Tableau independently of the dimensions displayed in a visualization. Understand FIXED, INCLUDE and EXCLUDE expressions with syntax, practical examples and business use cases.
1. Introduction to LOD Expressions
Level of Detail (LOD) Expressions in Tableau are used to calculate values at a specific level of detail, independently of or in relation to the dimensions available in the current visualization.
Normally, Tableau calculates aggregated measures according to the dimensions placed in a worksheet. However, business analysis sometimes requires calculations at a different level of detail.
For example, a sales dashboard may display total sales by region, but the business may also want to calculate total sales for each customer regardless of the region displayed in the visualization. LOD expressions make this type of analysis possible.
Why Are LOD Expressions Used?
- Calculate values at a specific level of detail.
- Calculate customer-level sales independently of the view.
- Compare individual customer performance with regional totals.
- Calculate averages at a different level of granularity.
- Analyze sales at customer, product, region or order level.
- Create more flexible and advanced business calculations.
- Control the level at which a measure is evaluated.
2. What Is Level of Detail?
Level of Detail refers to the level at which data is grouped or summarized. In Tableau, dimensions such as Customer Name, Product, Region and Order Date determine the granularity of a visualization.
For example, if Region is placed in a worksheet, Tableau can calculate total sales separately for each region. If Customer Name is added, the data can be divided further into customer-level records.
Understanding Different Levels of Detail
Region Level
Sales summarized by region.
Region + SUM(Sales)
Customer Level
Sales summarized by customer.
Customer + SUM(Sales)
Product Level
Sales summarized by product.
Product + SUM(Sales)
Order Level
Sales summarized by order.
Order ID + SUM(Sales)
Example
Consider a dataset containing sales transactions for different customers and regions. When Region is used in the view, Tableau groups the sales values by region.
Rows: Region
Columns: SUM(Sales)
When Customer Name is added to the view, the visualization can display sales for individual customers within each region.
Rows: Region, Customer Name
Columns: SUM(Sales)
The level of detail changes according to the dimensions included in the visualization.
3. Why Do We Need LOD Expressions?
Ordinary aggregation functions such as SUM(), AVG(), MIN() and MAX() calculate results based on the data grouping used in the visualization. Sometimes, the required calculation must be performed at a different level from the current view.
Consider a business dashboard that displays total sales by region. Management also wants to know the total sales generated by each customer, even when Customer Name is not displayed in the visualization.
Without LOD Expressions
Rows: Region
Columns: SUM(Sales)
Tableau displays the aggregated sales for each region. It does not directly display the separate customer-level sales totals in this regional view.
With LOD Expressions
{ FIXED [Customer Name] : SUM([Sales]) }
Tableau calculates total sales for each customer independently of the dimensions displayed in the view. The resulting customer-level value can then be used in other analyses.
4. Types of LOD Expressions in Tableau
Tableau provides three main types of Level of Detail expressions: FIXED, INCLUDE and EXCLUDE. Each type controls the calculation granularity differently.
FIXED
Calculates values using the dimensions explicitly specified in the expression, independently of the dimensions in the view.
{ FIXED [Dimension] : AGG([Measure]) }
INCLUDE
Adds the specified dimension to the level of detail used for the calculation.
{ INCLUDE [Dimension] : AGG([Measure]) }
EXCLUDE
Removes the specified dimension from the view's level of detail for the calculation.
{ EXCLUDE [Dimension] : AGG([Measure]) }
| LOD Type | Behaviour | Common Use |
|---|---|---|
| FIXED | Uses explicitly specified dimensions. | Customer-level or region-level calculations. |
| INCLUDE | Adds dimensions to the view's level of detail. | Calculating customer-level values inside a regional view. |
| EXCLUDE | Removes dimensions from the view's level of detail. | Calculating regional totals while viewing individual products. |
5. General Syntax of LOD Expressions
LOD expressions use curly braces and a keyword that identifies the calculation type. The expression specifies the dimensions and the aggregation to be performed.
FIXED Syntax
{ FIXED [Dimension] : AGG([Measure]) }
INCLUDE Syntax
{ INCLUDE [Dimension] : AGG([Measure]) }
EXCLUDE Syntax
{ EXCLUDE [Dimension] : AGG([Measure]) }
Understanding the Syntax
| Part | Meaning |
|---|---|
| { } | Curly braces define the LOD expression. |
| FIXED | Specifies a fixed calculation level. |
| INCLUDE | Specifies dimensions to add to the calculation level. |
| EXCLUDE | Specifies dimensions to remove from the view's calculation level. |
| [Dimension] | The dimension used to define the calculation granularity. |
| AGG() | An aggregation function such as SUM(), AVG(), MIN() or MAX(). |
| [Measure] | The numeric field being calculated. |
6. FIXED LOD Expression
FIXED LOD expressions calculate values using the dimensions explicitly specified in the expression. The calculation does not depend on the dimensions currently displayed in the view.
This makes FIXED useful when a calculation must remain at a specific level of detail even when users add or remove other dimensions from the worksheet.
Syntax
{ FIXED [Dimension] : AGG([Measure]) }
Example: Customer-Level Sales
{ FIXED [Customer Name] : SUM([Sales]) }
This expression calculates the total sales generated by each customer, regardless of the other dimensions displayed in the visualization.
Example Dataset
| Customer | Region | Sales |
|---|---|---|
| Omkar | East | 10000 |
| Omkar | East | 15000 |
| Rahul | West | 20000 |
| Rahul | West | 10000 |
| Priya | South | 12000 |
Result of FIXED Calculation
{ FIXED [Customer Name] : SUM([Sales]) }
| Customer | Customer-Level Sales |
|---|---|
| Omkar | 25000 |
| Rahul | 30000 |
| Priya | 12000 |
How to Create a FIXED LOD Calculation
- Open a Tableau worksheet.
- Go to the Data pane.
- Right-click and select Create Calculated Field.
- Enter the name Customer Level Sales.
- Write the following expression:
{ FIXED [Customer Name] : SUM([Sales]) }
- Click Apply or OK.
- Drag Customer Name to Rows.
- Drag Customer Level Sales into the view.
- Verify the calculated customer-level sales values.
7. INCLUDE LOD Expression
INCLUDE LOD expressions add one or more specified dimensions to the level of detail used for a calculation. The calculation is performed at the more detailed level and then aggregated to the level of the visualization.
INCLUDE is useful when you want to calculate detailed values that are not directly displayed in the view.
Syntax
{ INCLUDE [Dimension] : AGG([Measure]) }
Example: Customer-Level Sales
{ INCLUDE [Customer Name] : SUM([Sales]) }
Suppose Region is displayed in the visualization, but the business wants to calculate sales at the customer level within each region before aggregating those results.
Example
| Customer | Region | Sales |
|---|---|---|
| Omkar | East | 10000 |
| Omkar | East | 15000 |
| Rahul | West | 20000 |
| Rahul | West | 10000 |
| Priya | South | 12000 |
If Region is used in the view and Customer Name is included in the calculation, Tableau calculates customer-level sales within each region and then aggregates those results at the region level.
{ INCLUDE [Customer Name] : SUM([Sales]) }
Expected Customer-Level Values
| Region | Customer | Customer Sales |
|---|---|---|
| East | Omkar | 25000 |
| West | Rahul | 30000 |
| South | Priya | 12000 |
How to Create an INCLUDE Calculation
- Open a worksheet.
- Right-click in the Data pane.
- Select Create Calculated Field.
- Enter the name Customer Sales Include.
- Write the expression:
{ INCLUDE [Customer Name] : SUM([Sales]) }
- Click OK to save the calculation.
- Drag Region to Rows.
- Drag Customer Sales Include into the view.
- Review how customer-level results are aggregated at the region level.
8. EXCLUDE LOD Expression
EXCLUDE LOD expressions remove one or more specified dimensions from the level of detail of the current visualization for the calculation.
It is useful when a visualization contains detailed dimensions, but a calculation must be performed at a higher level of detail.
Syntax
{ EXCLUDE [Dimension] : AGG([Measure]) }
Example: Sales Excluding Product
{ EXCLUDE [Product] : SUM([Sales]) }
Suppose Product and Region are both displayed in the view. EXCLUDE Product removes Product from the calculation level, allowing the calculation to return regional sales totals for the product-level marks.
Example Dataset
| Region | Product | Sales |
|---|---|---|
| East | Laptop | 50000 |
| East | Mobile | 30000 |
| West | Laptop | 40000 |
| West | Mobile | 20000 |
When Region and Product are both displayed, the ordinary SUM(Sales) calculation returns sales for each region-product combination.
{ EXCLUDE [Product] : SUM([Sales]) }
Expected Result
| Region | Product | Regional Sales Excluding Product |
|---|---|---|
| East | Laptop | 80000 |
| East | Mobile | 80000 |
| West | Laptop | 60000 |
| West | Mobile | 60000 |
How to Create an EXCLUDE Calculation
- Open a Tableau worksheet.
- Right-click the Data pane.
- Select Create Calculated Field.
- Enter the name Regional Sales Excluding Product.
- Write the expression:
{ EXCLUDE [Product] : SUM([Sales]) }
- Save the calculated field.
- Drag Region to Rows.
- Drag Product to Rows or Columns.
- Add the calculated field to the view.
- Compare regional sales with individual product sales.
9. Difference Between FIXED, INCLUDE and EXCLUDE
Although all three LOD expressions control calculation granularity, they work differently. Understanding these differences helps select the correct expression for a business requirement.
| Feature | FIXED | INCLUDE | EXCLUDE |
|---|---|---|---|
| Purpose | Calculate at explicitly specified dimensions. | Add dimensions to the view's level of detail. | Remove dimensions from the view's level of detail. |
| View dependency | Independent of dimensions in the view. | Uses the view's dimensions plus included dimensions. | Uses the view's dimensions except excluded dimensions. |
| Typical example | Customer-level sales. | Customer-level sales within a regional view. | Regional sales within a product-level view. |
| Common use | Fixed-level calculations. | More detailed calculations. | Less detailed calculations. |
Visual Comparison
FIXED
Calculation level is explicitly specified.
INCLUDE
Adds more detail to the calculation.
EXCLUDE
Removes detail from the calculation.
10. LOD Expressions and Aggregation Functions
LOD expressions commonly use aggregation functions such as SUM(), AVG(), MIN() and MAX() inside their expressions. The aggregation determines how the measure is calculated at the specified level of detail.
Example: Customer Sales
{ FIXED [Customer Name] : SUM([Sales]) }
This expression calculates total sales for each customer.
Example: Average Customer Sales
{ FIXED [Customer Name] : AVG([Sales]) }
This expression calculates the average transaction sales for each customer.
Example: Maximum Customer Transaction
{ FIXED [Customer Name] : MAX([Sales]) }
This expression calculates the highest sales transaction for each customer.
11. LOD Expressions and Tableau Filter Order
Tableau uses an order of operations to determine when filters and calculations are evaluated. Understanding this order is especially important when working with FIXED LOD expressions.
FIXED LOD expressions are evaluated before ordinary dimension filters. Context filters are evaluated before FIXED expressions. This means a FIXED calculation may include records that are hidden by a regular dimension filter.
Simplified Order of Operations
Simplified view of important Tableau operations. Tableau's complete order of operations contains additional stages.
Example: FIXED and Dimension Filters
Suppose a FIXED expression calculates total sales for each customer. If a regular Region dimension filter is applied afterward, the FIXED expression can still reflect records from regions hidden by that filter.
Using a Context Filter
If the Region filter is added to context, it is evaluated before the FIXED expression. The FIXED calculation then uses the records remaining after the context filter.
12. LOD Expressions vs Table Calculations
LOD expressions and table calculations both support advanced analytical operations, but they operate at different stages and serve different purposes.
| Feature | LOD Expressions | Table Calculations |
|---|---|---|
| Purpose | Control calculation granularity. | Perform calculations on values in the visualization. |
| Examples | FIXED, INCLUDE and EXCLUDE. | Running Total, Rank and Percent of Total. |
| Calculation level | Specified by LOD dimensions and view granularity. | Specified by addressing and partitioning. |
| Common use | Customer-level sales in a regional view. | Monthly cumulative sales. |
| Dependency | Depends on the LOD type and its dimensions. | Depends on the structure of the view. |
13. Practical Example: Customer and Regional Sales Analysis
Consider a company that wants to analyze customer sales performance across different regions. Management wants to see regional sales, customer-level sales and regional totals while examining individual products.
Step 1: Calculate Customer-Level Sales
{ FIXED [Customer Name] : SUM([Sales]) }
This calculates total sales for each customer.
Step 2: Calculate Customer Sales Within a Regional View
{ INCLUDE [Customer Name] : SUM([Sales]) }
This adds Customer Name to the view's calculation level so that sales can be calculated for individual customers before being summarized at the view level.
Step 3: Calculate Regional Sales Without Product Detail
{ EXCLUDE [Product] : SUM([Sales]) }
This removes Product from the calculation level, allowing regional sales totals to be compared with individual product values in the view.
Step 4: Create the Visualization
- Connect Tableau to the sales dataset.
- Open a new worksheet.
- Drag Region to Rows.
- Drag Product to Columns.
- Drag SUM(Sales) to the Marks card or Text.
- Add the customer-level calculated field to the view.
- Add the regional sales calculation for comparison.
- Use appropriate filters to analyze individual regions.
Example Sales Analysis
| Region | Product | Product Sales | Regional Sales |
|---|---|---|---|
| East | Laptop | 50000 | 80000 |
| East | Mobile | 30000 | 80000 |
| West | Laptop | 40000 | 60000 |
| West | Mobile | 20000 | 60000 |
Example of comparing product-level sales with regional totals.
14. Common Errors and Troubleshooting
| Problem | Possible Reason | Solution |
|---|---|---|
| FIXED result does not change with a filter | Ordinary dimension filters are evaluated after FIXED. | Check whether the filter should be a context filter. |
| Unexpected INCLUDE result | Included dimension changes the calculation granularity. | Review the dimensions in the view and the required aggregation. |
| EXCLUDE result appears repeated | The excluded dimension is still displayed in the view. | Check the intended calculation level and visualization. |
| LOD result is larger than expected | Repeated LOD values may be summed again. | Choose an appropriate outer aggregation. |
| Invalid LOD expression | Incorrect syntax, field name or aggregation. | Verify curly braces, dimensions and aggregation syntax. |
| Results change after modifying the view | INCLUDE or EXCLUDE depends on view dimensions. | Review the view's level of detail. |
15. Best Practices for LOD Expressions
- Understand the required calculation granularity before writing an LOD expression.
- Use FIXED when the calculation must use explicitly specified dimensions.
- Use INCLUDE when additional detail is needed in the calculation.
- Use EXCLUDE when a dimension must be removed from the view's calculation level.
- Choose meaningful names for calculated fields.
- Check filter order when working with FIXED expressions.
- Use context filters when the FIXED calculation should respect selected filters.
- Check for repeated values before applying an outer SUM aggregation.
- Validate results using a small sample dataset.
- Keep complex calculations documented for easier maintenance.
Summary
Level of Detail Expressions in Tableau provide control over the granularity at which calculations are performed. FIXED expressions calculate values using explicitly specified dimensions independently of the dimensions in the view, INCLUDE expressions add dimensions to the view's calculation level, and EXCLUDE expressions remove specified dimensions from that level. LOD expressions are useful for customer-level sales, regional comparisons, product analysis and other advanced business calculations. Understanding their syntax, aggregation behaviour and relationship with Tableau's filter order is essential for creating accurate visualizations. In the next lesson, you will learn how to create FIXED LOD Expressions in detail with practical examples.