Understanding Tableau EXCLUDE: A Comprehensive Guide

Summary of Tableau EXCLUDE

Tableau EXCLUDE is a powerful feature that allows users to remove specific dimensions from the level of detail used in calculations. This capability is particularly useful when you want to compute metrics at a broader level than what is currently displayed in the visualization. Common scenarios include calculating stable metrics like customer averages, regional totals, or percentages of totals while the dashboard is filtered by other fields.

Problem Definition: The Challenge with Dimensions in Tableau

In Tableau, visualizations are typically calculated based on the dimensions included in the view. While this approach works for many charts, it can become problematic when the analytical question is more nuanced than the dashboard layout allows.

Common Business Questions

Analysts often face questions such as:

• What is the customer average order value when the view is segmented by product?
• What is the regional revenue when both region and category are displayed?
• What are total sales excluding the impact of a date dimension in the view?

These scenarios highlight the need for calculations that ignore one or more dimensions in the visualization. Without the ability to exclude dimensions, results can fluctuate with changes in the view, leading to unstable comparisons and potentially misleading insights.

Key Concept: The Functionality of EXCLUDE

The EXCLUDE function allows you to calculate a metric while disregarding one or more dimensions currently present in the view.

How EXCLUDE Works

For example, if a visualization is grouped by Product and Region, an EXCLUDE expression can remove Product from the calculation, allowing the result to be computed solely at the Region level. This differs from standard aggregation, as the calculation does not strictly adhere to the visual grain. Unlike fixed calculations, EXCLUDE is responsive to the current view, adjusting by removing selected dimensions.

Data Example: Understanding EXCLUDE with Sales Data

Consider a small sales table:

When the visualization displays Product, the default measure is calculated by product:

Now, if we want to see regional sales while still displaying Product in the view, excluding Product from the calculation yields:

This result occurs because the total for each region remains constant across products.

Tool Implementation: How to Use EXCLUDE in Tableau

What an EXCLUDE LOD Expression Is

In Tableau, EXCLUDE is a Level of Detail (LOD) expression that removes specified dimensions from the view’s level of detail before performing the aggregation.

#### Syntax

{ EXCLUDE [Dimension] : Aggregation }

#### Example

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

This expression indicates that:

• The current view context is used.
• The Product dimension is ignored.
• The aggregation is calculated at the remaining level of detail.

If the view includes Region and Product, Tableau computes the measure as if Product were not present.

Applied Example: Calculating Regional Sales with Product Breakdown

Consider the following customer question:

What is the total sales for each region, repeated across products in that region?

Expression

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

Result

Explanation

• The view is segmented by Region and Product.
• The EXCLUDE function removes Product from the calculation.
• The result reflects total sales for each Region, with every product row in the same region displaying the same regional total.

This approach is beneficial when product details are necessary for context, but the metric itself should remain at a regional level.

Why EXCLUDE Exists: The Need for Flexible Calculations

Tableau typically aggregates data based on the dimensions present in the view. For instance, if the visualization includes Region and Product, it behaves like:

SUM([Sales]) by Region and Product

EXCLUDE allows users to override this default behavior, enabling calculations that utilize the current view while excluding specific dimensions. This feature is particularly useful when a dimension is included solely for display or drill-down purposes, rather than for the metric itself.

Without EXCLUDE, analysts often resort to duplicated worksheets or complex data modeling. EXCLUDE provides a straightforward solution at the calculation level.

Important Behavioral Details of EXCLUDE

EXCLUDE Depends on the Current View

EXCLUDE is not a fixed-grain calculation like FIXED. It adjusts based on the dimensions present in the visualization. Therefore, if the view changes, the result may also change, which is expected behavior.

Filters Can Change the Result

Since EXCLUDE operates within the context of the view, filters play a significant role. If a filter removes certain rows, the total calculated at the excluded level is based only on the remaining data. This consideration is crucial in dashboard design, as user filters can impact results even when the excluded dimension is not directly involved.

Difference from Table Calculations

EXCLUDE is computed before table calculations, functioning as a source-level aggregation rather than a post-aggregation display rule.

Repeated Values Across Rows

A common observation is that the same value may appear on multiple rows. This is not an error. If Product is excluded but still displayed in the view, every product within the same region will show the same regional result.

Stability in Denominators

EXCLUDE is often employed when the numerator varies by row, but the denominator should remain at a higher grain. This is frequently seen in ratio calculations, percent of total metrics, and contribution analysis.

Real Usage Patterns for EXCLUDE

Common use cases for EXCLUDE include:

• Displaying regional totals within a product view.
• Showing customer totals within an order-level chart.
• Calculating percent of category while viewing subcategory details.
• Providing stable denominators for ratios.
• Creating benchmark metrics that ignore drill-down fields.
• Repeating totals across rows for comparison.
• Designing layered dashboards where detail and summary coexist.

A practical approach is to use EXCLUDE when a dimension is useful for visualization but not meaningful for the metric. This often indicates that the calculation should occur at a different grain than the view.

Conclusion

Understanding and effectively utilizing the EXCLUDE function in Tableau can significantly enhance your analytical capabilities. By allowing you to control the level of detail in your calculations, EXCLUDE provides a flexible solution for addressing complex business questions while maintaining the integrity of your visualizations.