5.3 INCLUDE & EXCLUDE LOD Expressions: Dynamic Granularity

5.3 INCLUDE & EXCLUDE LOD Expressions: Dynamic Granularity

While FIXED Level of Detail expressions operate in complete isolation from the visual canvas, INCLUDE and EXCLUDE expressions are fundamentally view-dependent. They are designed to collaborate dynamically with the dimensions present on your worksheet shelves.

Understanding how INCLUDE injects dimensions to drill down into finer granularities and how EXCLUDE strips dimensions to roll up into coarser summaries is essential for solving advanced aggregation challenges on the Salesforce Certified Tableau Data Analyst exam.

---

The Concept of View-Dependent Dynamic Granularity

The fundamental architectural difference between FIXED and INCLUDE/EXCLUDE lies in how the computational grain is resolved:

• FIXED Scope = { Declared Dimensions } (The VizLOD is completely ignored).
• INCLUDE Scope = { VizLOD Dimensions } ∪ { Declared Dimensions } (Union: adds declared dimensions to the active view).
• EXCLUDE Scope = { VizLOD Dimensions } \ { Declared Dimensions } (Difference: removes declared dimensions from the active view).

Because INCLUDE and EXCLUDE build upon whatever dimensions are active on the Rows, Columns, and Marks shelves, their computational grain shifts dynamically as you add or remove dimensions during visual analysis. If you drag Category onto Columns, both INCLUDE and EXCLUDE automatically incorporate Category into their underlying queries.

Consequently, INCLUDE and EXCLUDE calculations can only exist as Measures. Tableau does not permit converting them into Dimensions because their values cannot be determined until the visualization layout is rendered.

---

Deep Dive: INCLUDE Expressions (Finer Granularity)

An INCLUDE LOD expression calculates an aggregate at a finer level of detail than the VizLOD. It instructs Tableau to include additional dimensions in the query aggregation that are not visually present on the worksheet.

The Classic Analytical Problem: Aggregation of an Aggregation

A business question asks: "What is the average customer sales volume across each State?"

If you place State on Rows and drag [Sales] to Columns with the aggregation set to Average (AVG([Sales])), Tableau calculates the average sales per transactional row (order line) within each state. If Customer A purchased 10 items for $5 each, their true customer spend is $50, but AVG([Sales]) records each $5 item independently. This significantly distorts customer purchasing power.

If you drag Customer ID to the Detail shelf, you alter the VizLOD. The single state bar shatters into thousands of tiny individual customer marks.

The INCLUDE Solution

To compute the true average customer sales per state without altering the visual mark, write:

// Average Customer Sales per State
{ INCLUDE [Customer ID] : SUM([Sales]) }

When this field is placed on the Columns shelf and aggregated as AVG(), Tableau executes a two-step nested aggregation:

1. Inner Query (Subquery at Finer Grain): Tableau queries the database grouping by { State, Customer ID } and calculates the sum of sales for each individual customer: SUM([Sales]).
2. Outer Query (View-Level Aggregation): Tableau aggregates those customer sums up to the State level using the outer aggregation specified on the pill: AVG(...).

+-----------------------------------------------------------------------------------------+
| State: California                                                                       |
|   ├── Customer 101: SUM(Sales) = $450.00  ---\                                          |
|   ├── Customer 102: SUM(Sales) = $150.00  ----+===> Outer AVG() = $300.00               |
|   └── Customer 103: SUM(Sales) = $300.00  ---/      (Displayed as 1 single clean bar)   |
+-----------------------------------------------------------------------------------------+

Other Common INCLUDE Use Cases

• Average Daily Sales per Month: {INCLUDE [Order Date] : SUM([Sales])} displayed at the Month level with AVG() aggregation.
• Maximum Order Size by Region: {INCLUDE [Order ID] : SUM([Sales])} displayed at the Region level with MAX() aggregation.

---

Deep Dive: EXCLUDE Expressions (Coarser Granularity)

An EXCLUDE LOD expression calculates an aggregate at a coarser level of detail than the VizLOD. It instructs Tableau to omit specific dimensions from the query aggregation that are currently active on the worksheet canvas.

The Classic Analytical Problem: Percent of Parent Category Total

A business worksheet displays Category and Sub-Category on the Rows shelf, with SUM([Sales]) on Columns. The analyst needs to calculate what percentage of the parent Category's total sales each Sub-Category represents:

$\text{Share of Category} = \frac{\text{Sub-Category Sales}}{\text{Parent Category Total Sales}}$

On this worksheet, the VizLOD is {Category, Sub-Category}. To compute the denominator (the Category total), Tableau must calculate the sum of sales while ignoring Sub-Category.

The EXCLUDE Solution

// Denominator: Exclude Sub-Category to get Category Total
{ EXCLUDE [Sub-Category] : SUM([Sales]) }

When Tableau evaluates this expression:

1. It inspects the VizLOD: { Category, Sub-Category }.
2. It subtracts [Sub-Category] as instructed by the EXCLUDE keyword.
3. The resulting computational grain is strictly { Category }.
4. Tableau sums the sales across the entire Category and replicates that identical category total across every sub-category row within that category.

To calculate the percentage of category total, the analyst writes:

SUM([Sales]) / ATTR({ EXCLUDE [Sub-Category] : SUM([Sales]) })

Why EXCLUDE Outperforms Table Calculations in Complex Layouts

While this calculation can also be accomplished using a table calculation (SUM([Sales]) / TOTAL(SUM([Sales]))), table calculations rely on fragile directional settings (Compute Using: Table (Down) or Pane (Down)). If a user rearranges pills, adds a dimension, or changes sorting, table calculations frequently produce incorrect results. EXCLUDE explicitly names the dimension to remove, guaranteeing robust calculation behavior regardless of visual reformatting.

---

Order of Operations: How INCLUDE & EXCLUDE React to Filters

Unlike FIXED expressions, which evaluate before standard Dimension Filters, INCLUDE and EXCLUDE expressions evaluate AFTER standard Dimension Filters:

  1. Context Filters (Grey Pills)
  2. FIXED LOD Expressions
  ===============================================================
  3. Dimension Filters (Standard Blue Pills)
  ===============================================================
  4. INCLUDE and EXCLUDE LOD Expressions
  5. Measure Filters (Green Pills)
  6. Table Calculations

Practical Filter Behavior Comparison

Consider what happens when an executive filters out the 'Tables' sub-category using a standard Dimension Filter:

• With {FIXED [Category] : SUM([Sales])}: FIXED evaluates before the dimension filter. The category total still includes Tables unless the filter is added to Context.
• With {EXCLUDE [Sub-Category] : SUM([Sales])}: EXCLUDE evaluates after the dimension filter. The category total automatically subtracts Tables, recalculating the category total using only the surviving sub-categories!

This makes EXCLUDE naturally responsive to dashboard quick filters without requiring authors to promote filters to Context.

---

Comprehensive Architectural Comparison: FIXED vs. INCLUDE vs. EXCLUDE

---

Common Exam Traps with INCLUDE and EXCLUDE

Trap 1: Attempting to Convert to Dimensions

Exam questions may ask which LOD types can be used to slice a view as a discrete dimension. Remember: Only FIXED can be a dimension. Right-clicking an INCLUDE or EXCLUDE field does not offer the option to convert to Dimension because their output depends dynamically on the worksheet layout.

Trap 2: Excluding Dimensions Not Present in the View

What happens if you write {EXCLUDE [Region] : SUM([Sales])} on a worksheet that only contains Category on Rows?

• Behavior: Because Region is not in the VizLOD, subtracting Region changes nothing. Tableau simply evaluates the expression at the current VizLOD ({Category}). The formula compiles and executes without error, but functions as a no-op.

Trap 3: Nested Aggregation Syntax Errors

Candidates often attempt to write nested aggregations directly inside the LOD syntax:

// INVALID - Triggers compiler error: cannot nest aggregations directly
{ INCLUDE [Customer ID] : AVG(SUM([Sales])) }

// VALID - Inner aggregation inside LOD; outer aggregation applied on shelf
AVG({ INCLUDE [Customer ID] : SUM([Sales]) })