Historical Context & Motivation
Before Tableau introduced Level of Detail (LOD) expressions in version 9.0, analysts faced a persistent problem: the granularity of every calculation was dictated solely by the dimensions present in the visualization. If your view displayed data at the product level, every aggregation—SUM, AVG, COUNT—was computed at the product level, with no straightforward way to reference a different grain. Workarounds existed—table calculations, data blending, custom SQL—but each introduced complexity, performance overhead, or fragility that made dashboard maintenance burdensome. For anyone accustomed to writing GROUP BY clauses in SQL, this limitation felt particularly constraining, because SQL lets you freely control the grouping level of any subquery or window function. LOD expressions were Tableau's answer to that gap, and the INCLUDE and EXCLUDE keywords gave analysts declarative control over which dimensions participate in an aggregation, independent of the view.
The central question that INCLUDE and EXCLUDE LOD expressions address is deceptively simple: how do you compute a metric at a different level of granularity than what is currently displayed, and then seamlessly blend it back into the view? Understanding the answer requires a precise mental model of how Tableau's query pipeline processes dimensions, aggregations, and filters—and that is exactly what this lesson develops.
Core Principles & Definitions
To wield INCLUDE and EXCLUDE LOD expressions effectively, you need to internalize a few foundational concepts about how Tableau constructs queries. Every visualization in Tableau implicitly defines a view-level granularity—the set of dimensions currently on the Rows, Columns, Color, Size, Detail, and Tooltip shelves. This view-level granularity determines the GROUP BY clause of the underlying query. LOD expressions let you override that implicit grouping by explicitly adding or removing dimensions from the aggregation context.
View-Level Granularity
GROUP BY clause in the generated SQL. Every standard aggregation (SUM, AVG, etc.) operates at this level.INCLUDE LOD
{ INCLUDE [Dim] : AGG(Measure) }. The result is then re-aggregated to the view level.EXCLUDE LOD
{ EXCLUDE [Dim] : AGG(Measure) }. The coarser value is then replicated across view rows.FIXED LOD (Reference)
Re-Aggregation
GROUP BY; EXCLUDE removes columns from GROUP BY. Both are relative to the current view, which makes them adaptive as you evolve your dashboard.Visual Explanation
The following diagram illustrates how INCLUDE and EXCLUDE modify the aggregation grain relative to the view. The center column represents the view-level granularity—the dimensions currently on the shelves. INCLUDE shifts the computation to a finer grain (more dimensions), while EXCLUDE shifts it to a coarser grain (fewer dimensions). After the LOD calculation runs at its specified grain, the result is mapped back to the view through re-aggregation or replication.
Notice the critical distinction in the lower boxes. When INCLUDE computes at a finer grain, there are more intermediate rows than the view requires, so Tableau must wrap the LOD result in an outer aggregation—typically AVG(), MIN(), or MAX()—to collapse the extra rows back to the view level. Conversely, when EXCLUDE computes at a coarser grain, there are fewer intermediate rows, so the coarser result is simply replicated (broadcast) to each row at the view grain. This behavior directly parallels how SQL window functions replicate partition-level aggregates across row-level results.
How INCLUDE and EXCLUDE Generate Queries
Understanding the SQL that Tableau generates from LOD expressions demystifies their behavior. Consider a view with [Region] and [Category] on the shelves. The view-level grain is GROUP BY Region, Category. An INCLUDE expression adds a dimension to that GROUP BY, while an EXCLUDE expression removes one. Tableau materializes the LOD computation as a subquery or common table expression (CTE) and then joins it back to the main query.
[Dimension] to the GROUP BY. The result is finer than the view, so an outer aggregation (e.g., AVG) is required when displayed.[Dimension] from the GROUP BY. The result is coarser than the view, so each coarser value is replicated across matching view rows.Generated SQL Analogy
For a view with dimensions [Region] and [Category], an INCLUDE expression like { INCLUDE [Product] : SUM([Sales]) } generates a subquery equivalent to:
AVG(lod_sales) or whichever outer aggregation you specify.{ EXCLUDE [Category] : SUM([Sales]) }, Tableau removes [Category] from the GROUP BY. The coarser Region-level total is then replicated to every (Region, Category) row in the view—similar to a SQL window function SUM(Sales) OVER (PARTITION BY Region).Detailed Breakdown: When to Use INCLUDE vs EXCLUDE
Choosing between INCLUDE and EXCLUDE depends on whether you need to drill deeper than the view or pull back to a broader perspective. In practice, the choice often maps to a specific analytical pattern: INCLUDE is ideal when you need per-entity metrics before summarizing (e.g., average order size per customer, then averaged across regions), while EXCLUDE shines when you want each row to reference a parent-level total (e.g., category sales as a share of regional total). The following diagram maps common analytical scenarios to the appropriate LOD type.
| Scenario | LOD Type | Expression Example | Outer Aggregation |
|---|---|---|---|
| Average order value per customer, displayed per region | INCLUDE | { INCLUDE [Customer ID] : SUM([Sales]) } | AVG(...) |
| Each sub-category's sales as a % of its category total | EXCLUDE | SUM([Sales]) / { EXCLUDE [Sub-Category] : SUM([Sales]) } | Not needed (result is at view grain) |
| Count distinct products per category, view at region level | INCLUDE | { INCLUDE [Category] : COUNTD([Product]) } | SUM(...) |
| Deviation of each month's sales from yearly average | EXCLUDE | SUM([Sales]) - { EXCLUDE [Month] : AVG([Sales]) } | Not needed |
Worked Example: Customer-Level Metrics in a Regional View
Suppose you have a Superstore-style dataset and a bar chart showing SUM([Sales]) per [Region]. Your goal is two-fold: (1) compute the average sales per customer in each region using INCLUDE, and (2) compute the percentage of total sales each region represents using EXCLUDE.
[Region] on Rows and SUM([Sales]) on Columns. The view grain is Region alone.{ INCLUDE [Customer ID] : SUM([Sales]) }. This tells Tableau to compute SUM(Sales) grouped by Region, Customer ID (the view dims plus the included dim).GROUP BY Region, Customer IDAVG as the aggregation. The pill reads AVG({ INCLUDE [Customer ID] : SUM([Sales]) }).AVG([Sales]) (which averages across individual rows/transactions, not customers). The INCLUDE keyword injected the per-customer grain, and the outer AVG collapsed it to the per-region view.{ EXCLUDE [Region] : SUM([Sales]) }. With Region removed, the effective grain is the entire dataset—one grand total.SUM([Sales]) / { EXCLUDE [Region] : SUM([Sales]) }. The numerator is the region-level total (view grain), and the denominator is the grand total (coarser grain, replicated to each region row).[Category] to the view, the denominator automatically becomes the total excluding Region but including Category—exactly what you'd want for a per-category share.Strengths, Limitations & Comparisons
| Characteristic | INCLUDE LOD | EXCLUDE LOD | FIXED LOD |
|---|---|---|---|
| Grain relative to view | Finer (adds dimensions) | Coarser (removes dimensions) | Independent (absolute) |
| Adaptability | Adapts when view dims change | Adapts when view dims change | Does NOT adapt |
| Filter pipeline position | After dimension filters | After dimension filters | Before dimension filters |
| Requires outer aggregation | Yes (AVG, MIN, MAX, etc.) | No (value replicated; aggregation is ATTR-like) | Depends on context |
| Performance | Can be expensive with high-cardinality dims | Generally efficient (fewer groups) | Varies; often most efficient for simple cases |
| Typical use case | Per-entity metrics rolled up to the view | Percent-of-parent, benchmarks, deviations | Cohort assignment, date-based bucketing |
Strengths of INCLUDE/EXCLUDE
- View-adaptive: Both INCLUDE and EXCLUDE automatically adjust their effective grain when you add or remove dimensions from the view. This makes dashboards more maintainable than FIXED-based approaches for view-relative calculations.
- Filter-respecting: Because they sit after dimension filters in the pipeline, INCLUDE and EXCLUDE respect the user's filter selections—a behavior that analysts usually expect.
- Composable: You can nest LOD expressions inside other calculations, use them in IF statements, or combine INCLUDE and EXCLUDE results in a single formula.
Limitations and Pitfalls
- Outer aggregation confusion: INCLUDE results must be wrapped in an outer aggregation. Forgetting this—or using the wrong one—is the most common source of errors. AVG, MIN, and MAX produce very different results.
- Performance with high cardinality: INCLUDE on a high-cardinality dimension (e.g., Customer ID with millions of distinct values) forces Tableau to compute a much larger intermediate result set, which can degrade query performance.
- EXCLUDE on the last dimension: If you EXCLUDE the only dimension in the view, the grain becomes the entire dataset. This is valid but can be confusing to interpret.
GROUP BY modifications scoped to a single calculated field. INCLUDE is analogous to a correlated subquery that joins on all view dimensions plus extra ones; EXCLUDE is analogous to a PARTITION BY in a window function where you've deliberately omitted a column. The key advantage over FIXED is adaptability: as the dashboard evolves, these expressions evolve with it.Connection to Advanced LOD Patterns
INCLUDE and EXCLUDE LOD expressions form the foundation for several advanced analytical patterns in Tableau. Once you have mastered the basic mechanics, you can compose these expressions to handle complex, multi-grain calculations that would otherwise require custom SQL or data model changes. The following table contrasts basic usage with advanced patterns that build on the same principles.
| Basic Pattern | Advanced Extension | Description |
|---|---|---|
{ INCLUDE [Cust] : SUM(Sales) } | Nested LOD: INCLUDE inside FIXED | Compute per-customer sales first, then fix at a cohort level. E.g., { FIXED [Cohort] : AVG({ INCLUDE [Cust] : SUM(Sales) }) } |
{ EXCLUDE [Month] : SUM(Sales) } | Year-over-year difference with EXCLUDE | Exclude the month dimension to get an annual total, then compute each month's deviation from the yearly average—a dynamic variance analysis. |
| Simple percent-of-total | Multi-level percent-of-parent | Chain EXCLUDE expressions at different levels: exclude Sub-Category for category share, exclude Category for segment share. Each provides a different hierarchical benchmark. |
| Customer order count (INCLUDE) | Frequency binning | Use INCLUDE to compute order frequency per customer, then bin the result into "1 order", "2–5 orders", "6+ orders" groups for a histogram at the view level. |
As you move toward more sophisticated Tableau development—building parameter-driven dashboards, implementing row-level security, or designing reusable calculated fields—the ability to reason about grain management becomes indispensable. LOD expressions are, at their core, a way to declaratively manage grain in a visual analytics environment. This concept maps directly to dimensional modeling principles (Kimball methodology), where fact tables exist at specific grains and bridge tables mediate between grains. If you continue into data engineering or analytics engineering (e.g., dbt, LookML), you will encounter the same grain-management challenges at the modeling layer, and the mental model you build here transfers directly.
Practice Problems
[Category] on Rows and SUM([Profit]) on Columns. Write an INCLUDE LOD expression that computes the total profit per customer within each category, and then the correct pill expression to display the average of those customer-level profits.[Region] and [Sub-Category] on Rows with SUM([Sales]) on Columns. Write an EXCLUDE LOD expression to compute each sub-category's sales as a percentage of its region's total sales. Then explain what happens to the denominator if you remove [Region] from the view.{ EXCLUDE [State] : SUM([Sales]) } to show each state's sales as a share of regional total. However, when the user selects specific states in a dimension filter, the denominator (regional total) shrinks to only include the filtered states, distorting the percentages. Propose and justify a solution using either context filters or a different LOD type, and analyze the trade-offs of each approach.Lesson Summary
INCLUDE LOD expressions add dimensions to the view-level granularity, computing aggregations at a finer grain. The result must be wrapped in an outer aggregation (AVG, MIN, MAX) to collapse back to the view level. EXCLUDE LOD expressions remove dimensions, computing at a coarser grain and replicating the result to view rows. Both are view-adaptive—they automatically adjust when you modify the dimensions on the shelves—and both execute after dimension filters in Tableau's filter pipeline.
The choice between INCLUDE and EXCLUDE maps to a clear decision: if you need to drill to a finer grain (e.g., per-customer metrics before regional rollup), use INCLUDE. If you need to reference a coarser grain (e.g., percent-of-parent, deviation from group average), use EXCLUDE. For absolute grain control independent of the view, use FIXED. These three LOD keywords, combined with Tableau's filter pipeline, give you precise, declarative control over aggregation granularity—a capability that parallels SQL's GROUP BY and window functions but within a visual interface.