Understanding EXCLUDE Expressions in Tableau

Topic: Tableau Level of Detail (LOD) Expressions | Focus: EXCLUDE syntax, calculation granularity, aggregation, filters, percentage of total, comparisons, practical exercises, and interview questions.

Course: JustAcademy Tableau Training | Register for a Tableau Course Demo

1. Introduction to EXCLUDE Expressions

An EXCLUDE expression is a type of Level of Detail (LOD) expression in Tableau. It removes one or more specified dimensions from the view's level of detail for the purpose of a calculation. This allows Tableau to calculate a value at a less detailed level than the visualization currently displays.

For example, imagine a chart showing monthly sales by region. You want to display each region's monthly sales while also comparing them with total sales across all regions for the same month. An EXCLUDE expression can remove Region from the calculation, producing a monthly total that can be compared with each regional value.

Why use EXCLUDE expressions?

• Compare detailed values with totals at a higher level.
• Calculate overall totals while displaying more detailed dimensions.
• Build percentage-of-total calculations.
• Compare individual values with averages across groups.
• Analyze monthly totals without splitting the calculation by region.
• Remove a dimension from a calculation without removing it from the visualization.

Key idea: EXCLUDE removes specified dimensions from the view's calculation level, but the excluded dimensions can remain visible in the visualization.

2. Syntax of an EXCLUDE Expression

The general syntax is:

{ EXCLUDE [Dimension] : AGGREGATE([Measure]) }

Example:

{ EXCLUDE [Region] : SUM([Sales]) }

This expression calculates sales using the dimensions in the view, excluding Region from the calculation.

Understanding each component

The aggregate expression must contain an aggregation such as SUM, AVG, MIN, MAX, COUNT, or COUNTD. Tableau does not support ATTR or table calculations inside the aggregate expression of an LOD calculation.

3. How EXCLUDE Expressions Work

Suppose your worksheet displays Region and Month, with SUM(Sales) showing monthly sales for each region. You want to calculate the monthly sales total across all regions while retaining Region and Month in the visualization.

Create this calculated field:

{ EXCLUDE [Region] : SUM([Sales]) }

Tableau uses the dimensions in the view except Region when calculating this field. If Month remains in the view, the calculation returns the total sales for each month across regions.

Calculation process

1. The view contains Region and Month.
2. EXCLUDE removes Region from the calculation's level of detail.
3. Tableau calculates sales at the remaining Month level.
4. The monthly result is displayed alongside each regional mark for that month.
5. You can compare regional sales with the overall monthly total.

Important: EXCLUDE does not remove Region from the visualization. It removes Region only from the calculation's level of detail.

4. Example Dataset: Monthly Sales by Region

Consider this simplified dataset:

Step 1: Calculate monthly sales across regions

{ EXCLUDE [Region] : SUM([Sales]) }

Step 2: Understand the result

The monthly total is repeated for the regional marks within each month. This repetition is expected because Region is excluded from the calculation, while Month remains part of the view's calculation level.

5. Creating an EXCLUDE Expression in Tableau

1. Open Tableau Desktop or another Tableau authoring environment that supports LOD expressions.
2. Connect to a dataset such as Sample - Superstore.
3. Open a new worksheet.
4. Select Analysis > Create Calculated Field.
5. Name the field Sales Excluding Region.
6. Enter the formula:

{ EXCLUDE [Region] : SUM([Sales]) }

7. Click OK to save the calculation.
8. Drag Order Date to Columns and choose the Month/Year date level.
9. Drag Region to Rows or Color.
10. Drag SUM(Sales) to the view to display sales by month and region.
11. Drag Sales Excluding Region into the view to compare each region's sales with the monthly total across regions.

Expected result: You should be able to see each region's monthly sales alongside a value calculated without Region as a dimension.

6. EXCLUDE With One Dimension

The simplest EXCLUDE expressions remove one dimension from the view's calculation level.

Example 1: Sales Without Region

{ EXCLUDE [Region] : SUM([Sales]) }

Calculates sales at the level of the other dimensions in the view, without Region.

Example 2: Sales Without Category

{ EXCLUDE [Category] : SUM([Sales]) }

If the view contains Region and Category, this calculation returns sales by Region without splitting the calculation by Category.

Example 3: Sales Without Customer

{ EXCLUDE [Customer Name] : SUM([Sales]) }

If Customer Name is included in the view, this calculation removes Customer Name from the calculation level while retaining the other view dimensions.

Example 4: Profit Without State

{ EXCLUDE [State] : SUM([Profit]) }

Calculates profit at the remaining view dimensions, without splitting the calculation by State.

7. EXCLUDE With Multiple Dimensions

You can exclude more than one dimension by separating the field names with commas.

{ EXCLUDE [Region], [Category] : SUM([Sales]) }

This removes both Region and Category from the view's level of detail for the calculation.

Practical example

Suppose the view contains Region, Category, and Month. You want a monthly sales total without splitting it by Region or Category.

{ EXCLUDE [Region], [Category] : SUM([Sales]) }

The calculation uses the remaining Month dimension, so it returns the total sales for each month across the regions and categories in the applicable data scope.

Common applications

• Monthly totals across all regions and categories.
• Department totals without employee-level detail.
• Product totals without customer-level detail.
• Sales totals across multiple groups.
• Comparing detailed data with a broader business benchmark.

8. EXCLUDE and View Granularity

Granularity means the level of detail represented by the marks in a visualization. EXCLUDE removes one or more dimensions from the view's level of detail for the calculation, creating a less detailed result.

EXCLUDE results are less detailed than or equal in detail to the view. Values are commonly repeated across marks where the excluded dimension would otherwise split the calculation. Tableau generally defaults EXCLUDE fields to ATTR when placed in the view to represent this behavior.

9. Calculating Percentage of Total With EXCLUDE

EXCLUDE is useful for calculating the percentage of a broader total while retaining detailed marks in the visualization.

Example: Regional Sales as a Percentage of Monthly Sales

Suppose the view contains Month and Region. First create the monthly total:

{ EXCLUDE [Region] : SUM([Sales]) }

Name the field Monthly Sales Total.

Then create the percentage calculation:

SUM([Sales]) / ATTR([Monthly Sales Total])

Depending on how Tableau assigns the aggregation to the LOD field in your worksheet, you may instead use the automatically generated aggregate shown for that field. ATTR is a common choice for a repeated value at a coarser level of detail.

Example calculation

Suppose January sales for West are 1,000 and total January sales across regions are 4,500.

Percentage of Monthly Sales = 1000 / 4500
                            = 0.2222
                            = 22.22%

West contributes approximately 22.22% of January sales. Format the percentage field as Percentage to display the result clearly.

Note: This example assumes that the denominator represents the monthly total across the regions included by the relevant filters and data scope.

10. Comparing Values With an Overall Average

EXCLUDE can be used to compare a detailed mark with an average calculated without one of the view's dimensions.

Example: Compare Regional Sales With Monthly Average Sales

If the view contains Month and Region, first calculate the total monthly sales without Region:

{ EXCLUDE [Region] : SUM([Sales]) }

To calculate the average of monthly totals over a selected period, you need to define the intended date range and ensure that the calculation averages the monthly totals rather than the underlying transaction records.

For a more controlled approach, create a month-level total using FIXED and then exclude Month from the view-level calculation:

{ EXCLUDE [Order Date (Month / Year)] :
    AVG(
        { FIXED [Order Date (Month / Year)] : SUM([Sales]) }
    )
}

This nested LOD expression calculates sales by month and then averages those monthly totals across the applicable data scope. The month field in the declaration must match the actual date dimension used in the worksheet.

Example calculation

Suppose the monthly totals are 4,500, 5,000, and 5,500.

Average Monthly Sales = (4500 + 5000 + 5500) / 3
                      = 15000 / 3
                      = 5000

The average monthly total is 5,000. You can compare individual monthly values with this benchmark to identify months that perform above or below average.

Important: Averaging monthly totals is different from averaging individual transaction values. Always choose the level of detail that matches the business question.

11. EXCLUDE With Date Dimensions

Date dimensions are often used with EXCLUDE to calculate totals at a broader time level.

Example: Monthly Sales Without Region

{ EXCLUDE [Region] : SUM([Sales]) }

When Month and Region are in the view, this calculates monthly sales totals without the regional split.

Example: Sales Without Month

{ EXCLUDE [Order Date (Month / Year)] : SUM([Sales]) }

If the view contains Month and another dimension, this removes the specified Month/Year dimension from the calculation level. The result depends on the other dimensions remaining in the view.

Tip: The dimension declared in an EXCLUDE expression should match the field used in the visualization. Tableau may treat a saved calculation and an equivalent-looking date expression as different fields if their definitions do not match.

12. EXCLUDE and Filters

EXCLUDE expressions are evaluated after ordinary dimension filters. As a result, dimension filters generally affect the data considered by an EXCLUDE calculation.

Example

Consider this expression:

{ EXCLUDE [Region] : SUM([Sales]) }

If you filter Category to show only Technology, the EXCLUDE calculation generally calculates the total without splitting by Region, but it uses the records remaining after the Category dimension filter.

Filter behavior to remember

• Extract filters: Restrict the data included in the extract.
• Data source filters: Restrict the data available from the data source.
• Context filters: Restrict data before the LOD calculation.
• Dimension filters: Applied before EXCLUDE calculations.
• Measure filters: Applied later in Tableau's order of operations.
• Table calculation filters: Applied after table calculations and generally hide marks rather than removing underlying records from earlier calculations.

Filter behavior should be checked against the exact view and data model. When an EXCLUDE result looks unexpected, compare it with and without the filter and verify which dimensions remain in the calculation.

13. EXCLUDE vs FIXED vs INCLUDE

Remember: Use EXCLUDE when you need to remove a dimension from the calculation while keeping it in the visualization.

14. Common Mistakes and Troubleshooting

Mistake 1: Expecting EXCLUDE to remove a dimension from the chart

Problem: Region still appears in the view after excluding it.

Solution: This is expected. EXCLUDE removes the dimension from the calculation level, not from the visualization itself.

Mistake 2: Summing repeated totals

Problem: Monthly totals are repeated for each region and the grand total becomes inflated.

Solution: Review the aggregation and the level of detail of the view. Use the repeated value appropriately rather than summing the same monthly total across every regional mark.

Mistake 3: Excluding a dimension that is not in the view

Problem: The calculation does not produce the expected change.

Solution: EXCLUDE is intended to remove dimensions from the view's calculation context. Confirm that the dimension is actually part of the view's level of detail.

Mistake 4: Using the wrong date field

Problem: An EXCLUDE expression does not remove the date detail shown in the worksheet.

Solution: Check whether the field on the shelf is a discrete Month/Year date, a continuous date, or a separate calculated field. Make the expression's declaration match the field used in the view.

Mistake 5: Confusing average monthly sales with average transaction sales

Problem: The result does not match the expected average.

Solution: Decide whether the goal is an average of transaction values, monthly totals, regional totals, or another defined metric. Aggregate at the appropriate level before calculating the average.

Mistake 6: Assuming EXCLUDE ignores dimension filters

Problem: The result changes after applying a filter.

Solution: This is generally expected because ordinary dimension filters are applied before EXCLUDE calculations.

15. Best Practices for EXCLUDE Expressions

• Define the business question before deciding which dimension to exclude.
• Confirm the dimensions present in the visualization.
• Use descriptive calculated-field names, such as Monthly Sales Across Regions.
• Check for repeated values when the excluded dimension remains visible.
• Choose the correct aggregation for percentage-of-total and average calculations.
• Verify filter behavior, especially when comparing totals against filtered subsets.
• Match date dimensions exactly between the view and the expression.
• Test the result in a simple worksheet before using it in a dashboard.
• Compare results against manually calculated examples.

16. Practical Exercise: Monthly Sales Across Regions

Objective: Build a chart showing regional sales alongside the total sales across regions for each month.

1. Connect to Sample - Superstore.
2. Create a calculated field named Monthly Sales Across Regions.
3. Enter this formula:

{ EXCLUDE [Region] : SUM([Sales]) }

4. Place Month/Year of Order Date in the view.
5. Place Region on Rows or Color.
6. Place SUM(Sales) in the view to display sales by region and month.
7. Add Monthly Sales Across Regions to Text or Tooltip.
8. Compare each region's sales with the total for that month.
9. Apply a Category filter and observe how the calculation responds.
10. Try excluding Category as well if your view includes it and your analysis requires a broader total.

Expected learning outcome: You should understand how EXCLUDE removes a dimension from the calculation while retaining it in the view, and how filter choices affect the result.

17. Practice Questions With Answers

Question 1: Calculate sales without Region.

{ EXCLUDE [Region] : SUM([Sales]) }

Question 2: Calculate sales without Category.

{ EXCLUDE [Category] : SUM([Sales]) }

Question 3: Calculate profit without State.

{ EXCLUDE [State] : SUM([Profit]) }

Question 4: Calculate sales without Region and Category.

{ EXCLUDE [Region], [Category] : SUM([Sales]) }

Question 5: What does EXCLUDE do when the view contains Month and Region?

Answer: An expression that excludes Region calculates at the remaining Month level. The monthly value can be repeated across the regional marks for that month.

Question 6: Why are EXCLUDE values sometimes repeated across multiple marks?

Answer: The calculation is less detailed than the view. Multiple marks can therefore display the same value calculated at the broader level.

Question 7: Do ordinary dimension filters affect EXCLUDE calculations?

Answer: Generally, yes. Dimension filters are evaluated before EXCLUDE calculations.

Question 8: How can you calculate a region's percentage of monthly sales?

Answer: Calculate monthly sales without Region using { EXCLUDE [Region] : SUM([Sales]) }, then divide the regional sales value by the monthly total. Use a suitable aggregation for the repeated denominator.

18. Tableau EXCLUDE Expressions Interview Questions and Answers

Q1. What is an EXCLUDE expression in Tableau?

An EXCLUDE expression is an LOD calculation that removes specified dimensions from the view's level of detail for the calculation.

Q2. What is the syntax of EXCLUDE?

{ EXCLUDE [Dimension] : AGGREGATE([Measure]) }

Q3. What is the main purpose of EXCLUDE?

It calculates a measure at a less detailed level than the current visualization while retaining the excluded dimensions in the view.

Q4. Can EXCLUDE remove multiple dimensions?

Yes. Multiple dimensions can be separated by commas.

Q5. How is EXCLUDE different from FIXED?

EXCLUDE uses the view's dimensions except the excluded ones. FIXED calculates using its explicitly declared dimensions independently of the view's dimensions.

Q6. How is EXCLUDE different from INCLUDE?

EXCLUDE removes dimensions from the view's calculation level, while INCLUDE adds dimensions to it.

Q7. Why are EXCLUDE values repeated across marks?

Because the calculation is less detailed than the view. Several marks can share the same broader-level result.

Q8. Can EXCLUDE be used for percentage-of-total calculations?

Yes. It can calculate a total without a dimension such as Region, allowing regional values to be compared with that broader total.

Q9. Do dimension filters affect EXCLUDE expressions?

Generally, yes. Ordinary dimension filters are evaluated before EXCLUDE calculations.

Q10. When should you use EXCLUDE?

Use EXCLUDE when you want to remove one or more dimensions from a calculation while keeping those dimensions in the visualization.

19. Quick Revision Checklist

• Understand the purpose of EXCLUDE expressions.
• Remember the syntax: { EXCLUDE [Dimension] : AGGREGATE([Measure]) }.
• Know how to exclude one dimension or multiple dimensions.
• Understand that the excluded dimension can remain visible in the chart.
• Know why values can repeat across marks.
• Practice monthly sales totals without Region.
• Understand percentage-of-total calculations.
• Know the differences between EXCLUDE, FIXED, and INCLUDE.
• Understand how dimension filters affect EXCLUDE calculations.
• Check the level of detail and aggregation before interpreting results.

20. Official Learning Resources

• Tableau Help: EXCLUDE Level of Detail Expressions
• Tableau Help: How Level of Detail Expressions Work
• Tableau Help: Filters and LOD Expressions
• Tableau Whitepaper: Understanding LOD Expressions

Conclusion

EXCLUDE expressions help you analyze data at a broader level without removing detail from the visualization. They are particularly useful for monthly totals, regional comparisons, percentage-of-total calculations, and comparisons with averages. To use EXCLUDE correctly, understand the view's dimensions, choose the right aggregation, and check how filters influence the calculation.

Continue learning with JustAcademy Tableau Training or register for a Tableau course demo.