INCLUDE and EXCLUDE LOD Expressions in Tableau
Understand how INCLUDE and EXCLUDE Level of Detail expressions control calculation granularity in Tableau. Learn their syntax, differences, practical sales examples, aggregation behaviour, filter order and business applications.
1. Introduction to INCLUDE and EXCLUDE LOD
INCLUDE and EXCLUDE are two important Level of Detail (LOD) expressions in Tableau. They allow users to control the level of detail used by a calculation in relation to the dimensions displayed in a visualization.
INCLUDE adds one or more dimensions to the level of detail of the view for a calculation. EXCLUDE removes specified dimensions from the view's level of detail for a calculation. Both expressions are useful when a business needs to analyze data at a different granularity from the current visualization.
Why Use INCLUDE and EXCLUDE?
- Calculate measures at a more detailed level.
- Calculate measures at a less detailed level.
- Analyze customer performance within regional reports.
- Compare product sales with category-level totals.
- Calculate average sales at customer or order level.
- Create flexible business reports and dashboards.
2. What Is an INCLUDE LOD Expression?
INCLUDE LOD expressions add specified dimensions to the dimensions already present in the view. Tableau performs the calculation at this more detailed level and then aggregates the result back to the visualization level.
INCLUDE is particularly useful when a worksheet displays summarized information but the calculation requires an additional level of detail.
INCLUDE Syntax
{ INCLUDE [Dimension] : AGG([Measure]) }
INCLUDE with Multiple Dimensions
{ INCLUDE [Dimension 1], [Dimension 2] : AGG([Measure]) }
Example
{ INCLUDE [Customer] : SUM([Sales]) }
Understanding the Syntax
| Component | Meaning |
|---|---|
| INCLUDE | Adds specified dimensions to the view's calculation level. |
| [Customer] | Dimension added to the calculation level. |
| SUM() | Aggregation function used to calculate the measure. |
| [Sales] | Measure being calculated. |
3. How Does INCLUDE Work?
Suppose a Tableau worksheet displays Region. The visualization calculates total sales for each region. However, management wants to calculate sales at the customer level within each region before summarizing the results.
INCLUDE can add Customer to the calculation level while keeping Region as the view dimension.
INCLUDE Calculation Process
Example
{ INCLUDE [Customer] : SUM([Sales]) }
If Region is in the view, Tableau calculates sales for each customer within each region and then aggregates the customer-level results at the regional level.
4. Practical Example: Customer Sales Using INCLUDE
Consider the following sales dataset. It contains customer transactions from different regions.
| Customer | Region | Sales |
|---|---|---|
| Omkar | East | 10000 |
| Omkar | East | 15000 |
| Rahul | West | 20000 |
| Rahul | West | 10000 |
| Priya | South | 12000 |
| Priya | South | 8000 |
Step 1: Create the Calculated Field
{ INCLUDE [Customer] : SUM([Sales]) }
Step 2: Understand the Customer-Level Results
| Region | Customer | Customer Sales |
|---|---|---|
| East | Omkar | 25000 |
| West | Rahul | 30000 |
| South | Priya | 20000 |
Step 3: Aggregate at Region Level
Because each region has one customer in this sample dataset, the resulting regional totals are:
| Region | Aggregated Customer Sales |
|---|---|
| East | 25000 |
| West | 30000 |
| South | 20000 |
How to Create an INCLUDE Calculation
- Open Tableau Desktop or Tableau Public.
- Connect to the sales dataset.
- Open a new worksheet.
- Right-click inside the Data pane.
- Select Create Calculated Field.
- Enter the name Customer Sales Include.
- Write the following expression:
{ INCLUDE [Customer] : SUM([Sales]) }
- Click Apply or OK.
- Drag Region to Rows.
- Drag Customer Sales Include into the view.
- Review the aggregated results.
5. Calculate Average Customer Sales Using INCLUDE
INCLUDE can also be used to calculate averages at a more detailed level than the current visualization.
Calculated Field
{ INCLUDE [Customer] : SUM([Sales]) }
To calculate average customer sales at the view level, aggregate this customer-level result using AVG.
AVG({ INCLUDE [Customer] : SUM([Sales]) })
Example Dataset
| Region | Customer | Customer Sales |
|---|---|---|
| East | Omkar | 25000 |
| East | Rahul | 15000 |
| West | Priya | 30000 |
| West | Akash | 10000 |
Average Customer Sales by Region
| Region | Calculation | Average |
|---|---|---|
| East | (25000 + 15000) / 2 | 20000 |
| West | (30000 + 10000) / 2 | 20000 |
6. INCLUDE with Multiple Dimensions
INCLUDE supports multiple dimensions. This is useful when a calculation requires additional detail from more than one field.
Syntax
{ INCLUDE [Dimension 1], [Dimension 2] : AGG([Measure]) }
Example: Customer and Product Sales
{ INCLUDE [Customer], [Product] : SUM([Sales]) }
Suppose the view contains Region. The expression adds Customer and Product to the calculation level. Tableau calculates sales for each customer-product combination within the region and then aggregates those results at the view level.
Business Applications
- Customer sales by product within a region.
- Product performance by customer segment.
- Detailed revenue analysis in summarized dashboards.
- Customer-product purchasing behaviour.
7. What Is an EXCLUDE LOD Expression?
EXCLUDE LOD expressions remove specified dimensions from the view's level of detail for a calculation. They allow users to calculate a measure at a less detailed level than the current visualization.
EXCLUDE is useful when a worksheet displays detailed information but the business also needs a higher-level summary alongside it.
EXCLUDE Syntax
{ EXCLUDE [Dimension] : AGG([Measure]) }
EXCLUDE with Multiple Dimensions
{ EXCLUDE [Dimension 1], [Dimension 2] : AGG([Measure]) }
Example
{ EXCLUDE [Product] : SUM([Sales]) }
Understanding the Syntax
| Component | Meaning |
|---|---|
| EXCLUDE | Removes specified dimensions from the view's calculation level. |
| [Product] | Dimension removed from the calculation level. |
| SUM() | Aggregation function applied to the measure. |
| [Sales] | Measure used in the calculation. |
8. How Does EXCLUDE Work?
Suppose a worksheet displays both Region and Product. Tableau normally calculates sales for each region-product combination. Management wants to display the total regional sales alongside each product.
EXCLUDE can remove Product from the calculation level, allowing Tableau to calculate the total sales for each region.
EXCLUDE Calculation Process
Example
{ EXCLUDE [Product] : SUM([Sales]) }
This expression calculates regional sales without considering Product as part of the calculation level. Product can still remain visible in the worksheet.
9. Practical Example: Regional Sales Using EXCLUDE
Consider the following dataset containing sales for different products across regions.
| Region | Product | Sales |
|---|---|---|
| East | Laptop | 50000 |
| East | Mobile | 30000 |
| West | Laptop | 40000 |
| West | Mobile | 20000 |
| South | Laptop | 25000 |
| South | Mobile | 15000 |
Step 1: Create the Calculated Field
{ EXCLUDE [Product] : SUM([Sales]) }
Step 2: Understand Regional Totals
| Region | Regional Sales |
|---|---|
| East | 80000 |
| West | 60000 |
| South | 40000 |
Step 3: Display Results by Product
| Region | Product | Product Sales | Regional Sales |
|---|---|---|---|
| East | Laptop | 50000 | 80000 |
| East | Mobile | 30000 | 80000 |
| West | Laptop | 40000 | 60000 |
| West | Mobile | 20000 | 60000 |
| South | Laptop | 25000 | 40000 |
| South | Mobile | 15000 | 40000 |
How to Create an EXCLUDE Calculation
- Open Tableau Desktop or Tableau Public.
- Connect to the sales dataset.
- Open a new worksheet.
- Right-click inside the Data pane.
- Select Create Calculated Field.
- Enter the name Regional Sales Excluding Product.
- Write the expression:
{ EXCLUDE [Product] : SUM([Sales]) }
- Click Apply or OK.
- Drag Region to Rows.
- Drag Product to Columns.
- Add SUM(Sales) to the view.
- Add Regional Sales Excluding Product to the visualization.
- Compare product sales with the regional total.
10. EXCLUDE with Multiple Dimensions
EXCLUDE can remove multiple dimensions from a calculation. This is helpful when a worksheet contains several detailed dimensions but a higher-level calculation is required.
Syntax
{ EXCLUDE [Dimension 1], [Dimension 2] : AGG([Measure]) }
Example
{ EXCLUDE [Product], [Customer] : SUM([Sales]) }
If Product and Customer are both dimensions in the view, this expression removes them from the calculation level. The remaining view dimensions determine the level at which the sales measure is calculated.
Business Applications
- Calculate regional sales without product and customer detail.
- Compare overall performance with detailed records.
- Create summary metrics in detailed dashboards.
- Analyze business performance at a higher level.
11. Calculate Average Sales Using EXCLUDE
EXCLUDE can be used to calculate averages at a higher level than the current visualization.
Calculated Field
{ EXCLUDE [Product] : SUM([Sales]) }
To calculate the average regional sales across the product marks, use an appropriate outer aggregation.
AVG({ EXCLUDE [Product] : SUM([Sales]) })
Example
| Region | Product | Regional Sales |
|---|---|---|
| East | Laptop | 80000 |
| East | Mobile | 80000 |
| West | Laptop | 60000 |
| West | Mobile | 60000 |
When the view contains Region and Product, the excluded Product calculation produces the same regional total on each product mark within that region. The outer AVG can summarize these repeated regional values at the desired view level.
12. Difference Between INCLUDE and EXCLUDE
INCLUDE and EXCLUDE both modify the view's calculation granularity, but they perform opposite operations.
| Feature | INCLUDE | EXCLUDE |
|---|---|---|
| Purpose | Adds dimensions to the calculation level. | Removes dimensions from the calculation level. |
| Granularity | More detailed. | Less detailed. |
| View dependency | Uses view dimensions plus included dimensions. | Uses view dimensions except excluded dimensions. |
| Example | Customer sales within a regional view. | Regional sales within a product-level view. |
| Common use | Detailed calculations. | Higher-level summaries. |
Visual Comparison
INCLUDE
EXCLUDE
13. Difference Between FIXED, INCLUDE and EXCLUDE
Tableau provides three main LOD expressions. Understanding their differences helps determine which expression should be used for a particular business requirement.
| Feature | FIXED | INCLUDE | EXCLUDE |
|---|---|---|---|
| Calculation level | Explicitly specified dimensions. | View dimensions plus included dimensions. | View dimensions excluding specified dimensions. |
| View dependency | Independent of view dimensions. | Dependent on view dimensions. | Dependent on view dimensions. |
| Granularity | Defined by expression. | More detailed than the view. | Less detailed than the view. |
| Filter behaviour | Evaluated before ordinary dimension filters. | Evaluated after ordinary dimension filters. | Evaluated after ordinary dimension filters. |
| Typical example | Customer-level sales. | Customer-level calculation in a regional view. | Regional total in a product-level view. |
14. INCLUDE, EXCLUDE and Tableau Filter Order
Tableau evaluates INCLUDE and EXCLUDE expressions after ordinary dimension filters. This means dimension filters generally affect the records used by these calculations.
Unlike FIXED expressions, INCLUDE and EXCLUDE do not normally require a context filter just to make an ordinary dimension filter affect their calculations.
Simplified Order of Operations
Simplified order highlighting the relative position of FIXED, INCLUDE, EXCLUDE and ordinary dimension filters.
Practical Filter Example
Suppose a user applies a Region filter to show only East. An INCLUDE expression calculating customer-level sales will work with the records remaining after the dimension filter.
Similarly, an EXCLUDE expression calculating regional totals will use the filtered data when the dimension filter is applied.
15. Common Errors and Troubleshooting
| Problem | Possible Reason | Solution |
|---|---|---|
| INCLUDE returns unexpected values | Additional dimensions change the calculation granularity. | Review the view dimensions and included fields. |
| EXCLUDE returns repeated totals | The excluded dimension remains visible in the view. | Check the expected calculation level and outer aggregation. |
| Average values are incorrect | Average of transactions and average of grouped values are different. | Choose the correct level for the average. |
| Calculation result changes unexpectedly | INCLUDE and EXCLUDE depend on view dimensions. | Review dimensions added to or removed from the worksheet. |
| Invalid LOD expression | Incorrect syntax or field references. | Verify braces, keywords, dimensions and aggregation. |
| Unexpected results after filtering | Filter order affects the calculation. | Check the filter type and order of operations. |
16. Best Practices for INCLUDE and EXCLUDE
- Understand the dimensions currently used in the worksheet.
- Use INCLUDE when additional detail is required for a calculation.
- Use EXCLUDE when a higher-level summary is required.
- Choose the correct aggregation function.
- Check whether the result is repeated across multiple marks.
- Test calculations with different combinations of view dimensions.
- Understand how dimension filters affect the calculations.
- Use descriptive names for calculated fields.
- Validate results using sample records.
- Avoid unnecessary complexity in calculated fields.
17. Practical Exercise
Use a sales dataset containing Customer, Region, Product, Order ID and Sales fields. Complete the following tasks to practice INCLUDE and EXCLUDE expressions.
Exercise 1: Customer Sales
Create an INCLUDE expression that calculates total sales at customer level.
{ INCLUDE [Customer] : SUM([Sales]) }
Exercise 2: Product Sales
Create an INCLUDE expression that calculates sales at customer and product level.
{ INCLUDE [Customer], [Product] : SUM([Sales]) }
Exercise 3: Regional Sales
Create an EXCLUDE expression that calculates regional sales without product-level detail.
{ EXCLUDE [Product] : SUM([Sales]) }
Exercise 4: Category-Level Analysis
Create an EXCLUDE expression that removes Sub-Category from a view containing Category and Sub-Category.
{ EXCLUDE [Sub-Category] : SUM([Sales]) }
Exercise 5: Compare Results
Create a worksheet using Region and Product. Compare ordinary SUM(Sales), INCLUDE calculations and EXCLUDE calculations.
18. Lesson Summary
INCLUDE and EXCLUDE are Tableau Level of Detail expressions that modify the calculation granularity in relation to the dimensions displayed in a visualization. INCLUDE adds specified dimensions to the view's calculation level, allowing more detailed calculations before the results are aggregated back to the view. EXCLUDE removes specified dimensions from the view's calculation level, making higher-level calculations possible while retaining detailed dimensions in the visualization. Both expressions are useful for customer-level analysis, regional sales comparisons, product performance reporting and advanced business dashboards. Understanding their syntax, aggregation behaviour, view dependency and filter order helps create accurate Tableau calculations.