INCLUDE & EXCLUDE LOD Expressions

Extend your FIXED LOD knowledge with INCLUDE and EXCLUDE Level of Detail expressions -- understand LOD syntax as a way to 'zoom in' or 'zoom out' from the current view's granularity, and troubleshoot the common 'cannot mix aggregate and non-aggregate' error.

12 minutesVideo LessonPDF notes

Watch INCLUDE & EXCLUDE LOD Expressions on YouTube

🎯 Free Guest Mode: You are learning for free. Sign in to save your completion progress and quiz answers.

Ready to continue?

Mark this lesson as complete when you're ready to proceed.

Key moments

LOD Mental Model — Introduces the camera lens analogy for zooming into details or zooming out for bigger-picture totals.
Three LOD Types & Syntax — Outlines FIXED, INCLUDE, and EXCLUDE behaviors and details the mandatory curly brace and colon syntax.
Single & Multi-Dimension FIXED — Demonstrates category and category-region sales calculations that remain unaffected by sub-category filters.
INCLUDE LOD Mechanics — Calculates average sales per product inside a category view and shows how sheet filters affect the results.
EXCLUDE LOD Implementation — Removes the region dimension to display category totals and contrasts its filter sensitivity against FIXED.
Percent of Total & Error Fix — Resolves mixed-aggregation errors using a table-scoped LOD to calculate category percent of total sales.

PDF notes

Bottom line: You will master Level of Detail (LOD) expressions in Tableau to compute values at granularities independent of your visualization's layout. You will be able to construct FIXED, INCLUDE, and EXCLUDE calculations to zoom into granular metrics, zoom out for category totals, and avoid common aggregation mismatch errors.

This lesson on INCLUDE & EXCLUDE LOD Expressions is hands-on and example-driven. You will master Level of Detail (LOD) expressions in Tableau to compute values at granularities independent of your visualization's layout. You will be able to construct FIXED, INCLUDE, and EXCLUDE calculations to zoom into granular metrics, zoom out for category totals, and avoid common aggregation mismatch errors.

What You'll Be Able To Do

Construct FIXED, INCLUDE, and EXCLUDE expressions using correct curly brace syntax.
Compute category-level aggregations in fine-grained views that bypass standard dimension filters using FIXED.
Calculate fine-grained sub-aggregations like average product sales within high-level category views using INCLUDE.
Eliminate specific view dimensions like Region from calculations while keeping them in the visualization using EXCLUDE.
Resolve the 'cannot mix aggregate and non-aggregate arguments' error by wrapping whole-dataset totals in table-scoped LODs.

Detailed Concept Walkthrough

1. LOD Architecture and Syntax Structure

LOD expressions act like a camera lens that adjusts the analytical zoom level independently of the dimensions placed on rows, columns, or marks cards.

Syntax Rule: Every LOD expression is wrapped in outer curly braces { }, starts with the scoping keyword (FIXED, INCLUDE, or EXCLUDE), followed by zero or more comma-separated grouping dimensions, a colon :, and an aggregate calculation.
Under the Hood: Tableau interprets the colon as the boundary separating the target level of dimensionality (grouping set) from the mathematical operation (SUM, AVG, COUNT).
Execution Flow: When processed, Tableau computes the aggregation grouped by the explicitly defined dimension set before merging the resulting scalar or row-level values back into the primary visualization pipeline.

// Standard LOD calculation template
{
  FIXED [Category], [Region] : SUM([Sales])
}

Key Takeaway: All LOD expressions share the uniform syntax: { [TYPE] [Dimension1], [Dimension2] : AGG([Field]) }.

2. FIXED Expressions and Dimension Independence

FIXED computes values exclusively at the specified dimension level, locking the computation and ignoring both unlisted view dimensions and standard dimension filters.

Mechanism: When specifying FIXED [Category] : SUM([Sales]), Tableau aggregates sales solely across each Category, returning identical values across every Sub-Category mark within that Category.
Filter Behavior: Standard dimension filters (such as excluding a specific Sub-Category or Product) do not reduce or alter the value produced by a FIXED expression.
Multi-Dimensional Grouping: Adding multiple dimensions separated by commas (e.g., [Category], [Region]) forces Tableau to compute independent totals for each unique dimension combination regardless of chart detail.

// Locks Sales total at the Category level
// Bypasses Sub-Category filters in the sheet
{
  FIXED [Category] : SUM([Sales])
}

Key Takeaway: FIXED establishes an absolute calculation boundary that operates independently of chart dimensions and standard filters.

3. INCLUDE Expressions for Finer Granularity

INCLUDE forces Tableau to consider a dimension not present in the visualization frame, calculating metrics at a more detailed level before aggregating them into the view.

Mechanism: In a high-level view (e.g., Category Sales), INCLUDE [Product Name] : AVG([Sales]) computes the average sales per individual product first, then aggregates those product averages to the category level.
Under the Hood: An INCLUDE calculation respects all dimensions and filters in the sheet; if a user filters out individual products, the recalculated grand total changes accordingly.
View Granularity Alignment: When the view is expanded down to the row level (e.g., adding [Order ID]), the INCLUDE calculation naturally converges to match the standard measure aggregation.

// Calculates average product sales within each category in the view
{
  INCLUDE [Product Name] : AVG([Sales])
}

Key Takeaway: INCLUDE adds hidden detail to higher-level views while remaining fully responsive to the chart's active filters and structure.

4. EXCLUDE Expressions for Broader Context

EXCLUDE removes specific dimensions that exist in the chart frame from the calculation, allowing high-level totals to display alongside detailed marks.

Mechanism: When a chart displays [Category] and [Region], writing EXCLUDE [Region] : SUM([Sales]) strips the region dimension from the calculation, outputting the overall category total across all regional marks.
Filter Responsiveness: Unlike FIXED, EXCLUDE evaluates after sheet filters are applied, meaning filtering out a region dynamically recalculates the category total.
Visual Application: This enables direct comparison between a specific segment (Region) and its broader grouping (Category) within the exact same visual frame.

// Removes Region from computation to display overall Category Sales
{
  EXCLUDE [Region] : SUM([Sales])
}

Key Takeaway: EXCLUDE subtracts visual dimensions from the calculation while maintaining full sensitivity to chart filters.

5. Table-Scoped LODs and Aggregation Mixing

Omitting dimension declarations entirely creates a table-scoped LOD that computes across the entire dataset, solving aggregation mismatch errors in complex ratios.

Mechanism: Writing { SUM([Sales]) } without a scope keyword or dimension calculates the grand total of sales across the entire dataset.
Syntax Rule: Combining an LOD calculated field like { FIXED [Category] : SUM([Sales]) } with a bare SUM([Sales]) produces a 'Cannot mix aggregate and non-aggregate arguments' error.
Resolution: Wrapping the denominator in { SUM([Sales]) } provides a uniform scalar value across all rows, enabling seamless percent-of-total calculations without table calculation dependencies.

// Category percent of total sales calculation
// Resolves aggregate / non-aggregate mixing errors
{
  FIXED [Category] : SUM([Sales])
} 
/ 
{
  SUM([Sales])
}

Key Takeaway: An empty dimension LOD ({ SUM([Field]) }) computes dataset-wide totals and resolves mixed-aggregation syntax errors.

Topics Covered in INCLUDE & EXCLUDE LOD Expressions

LOD Mental Model (0:00 - 0:45) — Introduces the camera lens analogy for zooming into details or zooming out for bigger-picture totals.
Three LOD Types & Syntax (0:45 - 1:26) — Outlines FIXED, INCLUDE, and EXCLUDE behaviors and details the mandatory curly brace and colon syntax.
Single & Multi-Dimension FIXED (1:26 - 2:35) — Demonstrates category and category-region sales calculations that remain unaffected by sub-category filters.
INCLUDE LOD Mechanics (2:35 - 3:45) — Calculates average sales per product inside a category view and shows how sheet filters affect the results.
EXCLUDE LOD Implementation (3:45 - 4:40) — Removes the region dimension to display category totals and contrasts its filter sensitivity against FIXED.
Percent of Total & Error Fix (4:40 - 5:50) — Resolves mixed-aggregation errors using a table-scoped LOD to calculate category percent of total sales.

Reference Cheat Sheet

{ FIXED [Dim] : AGG([Field]) } — Computes aggregate at exact dimension level, ignoring chart filters
```
{ FIXED [Category] : SUM([Sales]) }
```
{ FIXED [Dim1], [Dim2] : AGG([Field]) } — Calculates aggregate across multiple fixed dimensions simultaneously
```
{ FIXED [Category], [Region] : SUM([Sales]) }
```
{ INCLUDE [Dim] : AGG([Field]) } — Adds dimension to calculation while respecting chart filters
```
{ INCLUDE [Product Name] : AVG([Sales]) }
```
{ EXCLUDE [Dim] : AGG([Field]) } — Removes chart dimension from calculation while respecting filters
```
{ EXCLUDE [Region] : SUM([Sales]) }
```
{ AGG([Field]) } — Computes aggregate across entire dataset ignoring all dimensions
```
{ SUM([Sales]) }
```

Comparison Table

Feature / LOD Type	FIXED	INCLUDE	EXCLUDE
Dimension Scope	Explicitly defined only	View dimensions plus added	View dimensions minus excluded
Filter Sensitivity	Ignores standard dimension filters	Respects sheet filters	Respects sheet filters
Primary Use Case	Independent totals & baselines	Finer detail without splitting marks	Broader totals across existing marks

Common Pitfalls

Mistake: Expecting FIXED LODs to update when users adjust standard view filters. Avoid: Use EXCLUDE or context filters if the calculation must reflect sheet-level filtering.
Mistake: Dividing an LOD measure directly by SUM(Field) and triggering aggregate mismatch errors. Avoid: Enclose the total denominator in a table-scoped LOD like { SUM(Field) }.
Mistake: Relying on Quick Table Calculations to compute category-level percentages in sub-category views. Avoid: Build an explicit LOD calculation to lock the numerator aggregation to Category.

FAQs

Why does FIXED Category Sales show the same number when a Sub-Category is filtered out? FIXED expressions are evaluated before standard dimension filters in Tableau's order of operations, rendering them immune to chart-level filter exclusions.
What is the mathematical difference between AVG(Sales) and an INCLUDE AVG(Sales) LOD? Standard AVG(Sales) averages every underlying raw transaction row, whereas INCLUDE averages the sales per specified dimension first and then averages those group results.
When should I use EXCLUDE instead of FIXED? Use EXCLUDE when you want to remove a specific dimension from the calculation while ensuring the computed total still reacts dynamically to other active sheet filters.

Common pitfalls

Mistake: Expecting FIXED LODs to update when users adjust standard view filters. Avoid: Use EXCLUDE or context filters if the calculation must reflect sheet-level filtering.
Mistake: Dividing an LOD measure directly by SUM(Field) and triggering aggregate mismatch errors. Avoid: Enclose the total denominator in a table-scoped LOD like { SUM(Field) }.
Mistake: Relying on Quick Table Calculations to compute category-level percentages in sub-category views. Avoid: Build an explicit LOD calculation to lock the numerator aggregation to Category.

Level of Detail or LOD calculations can seem confusing, but I'm going to break them down so simply that you'll see your data in a whole new way. I'll show some examples in Tableau, but first, I'm going to demonstrate how LODs work. Imagine your Tableau chart is like a camera lens. By default, Tableau focuses on what's in the frame, but with LOD calculations, you can zoom in to see fine details and you can zoom out to see the bigger picture, regardless of what's in the frame. This flexibility is what makes LOD so powerful. They give you answers at the exact level of detail you need without altering your chart's view. To demonstrate this, I'll start off with a view that has blue bars to represent the sum of sales by region and yellow bars that use an LOD to show the total sales by region. Say you filter for a category like Technology. Tableau recalculates the totals and the sales number drops because it's only showing what's visible in the view. But with the LOD, it's like zooming out to see the full picture. The calculation locks in the total sales and ignores the filter completely. LODs let you control the level of detail in your calculation no matter what's in your view. This example that locks in the total uses a FIXED LOD. But there are three types of LODs. FIXED ignores the chart's dimensions and filters and calculates at a level you define. INCLUDE adds extra details to the calculation while still respecting the chart structure. And EXCLUDE removes details from the calculation, even if it's present in the chart. Before we jump into building the LODs in Tableau, we need to understand the structure, and it's not hard because they all have the same basic structure. You'll start and end every LOD with curly braces. This tells Tableau that we're using an LOD. Then the type will tell the calculation how to behave. You can use FIXED, INCLUDE, or EXCLUDE. The dimension is the field or fields that we're using to define the level of detail. This is where you'll add the field that you want to fix, add, or remove. Then add a colon to separate the dimensions from the calculation logic. The aggregate function specifies the math or logic, like SUM or COUNT, and this will determine how the data's combined. While the field defines what you want to calculate, like Sales. Now I'll show how to apply these using examples in Tableau with the Sample - Superstore data set. I'm starting off with a view that shows Sales by Sub-Category, and I want to know the total sales for each category. So even though the chart is at the Sub-Category level, I want to compute the sum of sales at the Category level of detail, and we can do this using an LOD. So I'm going to create a calculated field for the Category Sales. I'll start with the curly brace and the type of LOD, which is FIXED. And since I want Category level sales, I'll type the Category field, then a colon to separate the dimensions from the calculation logic, and to get the total sales, I'll type the sum of sales. I'll end it with a curly brace and hit OK. Now I can bring the Category Sales field into the Marks and the Category as well. And you can see the tooltip not only includes sub-category sales, but also the total sales for the category it belongs to. This gives us the big picture, which is category totals, while still keeping the details, sub-category sales, within the chart. And you can see the total sales for the category Office Supplies is about 700,000. What I mean by this calculation is independent of what's in the chart is that I can filter the chart to exclude one of the sub-categories within Office Supplies, like Appliances. And the total sales for the Office Supplies category doesn't change within the chart. This is because the FIXED LOD overrides filters and only calculates based on the conditions we define, whereas INCLUDE and EXCLUDE respect the filters in the chart, which I'll show later. So this is an example where I created an LOD using a FIXED condition with only one dimension, but I'll also show an example using multiple dimensions. I'll right-click on Category Sales to edit it, and this will be for Category Sales that also takes into account the Region. To add a dimension to this LOD, I'll type a comma after Category and then add the Region field. Now the calculation is grouping by Category and Region, and it's saying ignore what's in the chart and give me the total sales for each Category Region combination. So when I pull Region into the view, you can see that Art, which is in the Office Supplies category in the Central region, has the same value as Binders. Whereas Accessories is in the Technology category, so its Central sales total is different. Even though this chart has a sub-category level of detail, our calculation uses the Region and Category level of detail. The FIXED LOD is the one that I use the most because it lets you calculate at any level no matter what's in your chart. And while FIXED lets us ignore dimensions in the chart, INCLUDE respects the dimensions and filters in the chart and lets us add a dimension to the calculation. For the INCLUDE example, I'll start with a view that has Sales by Category and I want to know the average sales per product within each category. Basically, I want to add the product detail, or dimension, to the calculation without changing the chart. So I'll create a calculated field for the average sales per product. I'll start with the same structure by adding a curly brace, and this time I want to type INCLUDE. For the dimension, I'll use Product Name, type a colon, and I want the average sales. Then I'll end it with a curly brace. When I pull this field into the view, you can see the calculation is forcing Tableau to include Product, even though the chart only shows Category. It gives us the bigger picture, which is the category-level totals, with details like the product-level averages in the same view. I'll show the back end of the LOD on this table to explain how it's calculating. This table just has a sum of sales for each product, and I've filtered it by the category Furniture. When I pull in our new calculated field, you can see we get different numbers for each product, which is also going to make the totals different. To make these easier to compare, I'll change both of the aggregations to the average. And now you can see for each product we get the same number, but the grand totals are different. The difference is that the average of sales calculates the average across all the rows versus our LOD calculates the average sales for each product first, then averages those product-level averages. So when I add Order ID to the view, it's now displaying data at a row level, as in one row per transaction, and this causes our LOD to behave the same way the average of sales calculation does. I'll change these back to SUM, and to show how a filter affects INCLUDE, I'll add Product to the Filters and exclude one of the products within our Furniture category. So unlike the FIXED type that ignores filters, we can see INCLUDE respected the filter that we added, and that caused the grand total to drop. While INCLUDE adds dimensions for more detail, EXCLUDE removes dimensions to focus on broader trends. For the EXCLUDE LOD example, I'm starting with a view that has Sales by Category and Region, and I want the total sales for each category without considering regions. Basically, I want to remove the region detail from the calculation, even though the chart includes it. So I'll create a calculated field for the Category Sales ignoring region. I'll start with the same structure with a curly brace and then the LOD type EXCLUDE. I'll type the dimension I'm ignoring, which is Region, and a colon, and the math will be for the sum of sales, and I'll end it with a curly brace. I'll add this to the view and you can see that instead of calculating sales broken down by Category and Region, which is the default behavior in this chart, the LOD calculates the total sales at the Category level only, completely ignoring Region. And that's why all the region bars within one category show the same value. I'll show you how we can achieve the same result by using a FIXED LOD. I'll bring the Category Sales LOD that we created at the beginning into the Marks. And while our FIXED and EXCLUDE LOD show the same value, they behave differently. I'll show this by bringing Region into the filter and removing one of the regions. You can see the EXCLUDE LOD adjusts for the filter and ignores the region we removed in the calculation, whereas the FIXED LOD ignores filters, so it has the same value no matter what region we filter for. This last example combines LODs to demonstrate their true power, and it points out a common error and how to fix it. Like the first example, I'm starting with Sales by Sub-Category, and I want to know the category percent of total sales. So I want two distinct levels of aggregation. We already have the total sales at the sub-category level, so I'm going to quickly create the view of percent of total sales at the category level. And these are the values we're going to calculate within the LOD. I'll go back to our Sales by Sub-Category view and create a calculated field for the Category Percent of Total Sales. I'll start by getting the total sales for each category, which is holding fixed the category, take the sum of sales, just like we did in the first example. Then I need to divide this by the total sales across all categories. But if I just type the sum of sales, you can see we get this error that you can't mix aggregate and non-aggregate arguments. So to fix this, we can turn the sum of sales into an LOD. Since I'm not including any dimensions in the LOD, it's saying ignore all the dimensions in the view and calculate the total sum of sales across the entire data set. Now I'll bring our new calculated field into the view and the Category as well. Now we can see the percent of sales for each category. For example, since Appliances and Binders are both in the Office Supplies category, they have the same value for the Category Percent of Total Sales. An LOD was necessary to get these values because the view is currently at the sub-category level of detail. If we try to replicate these values by dragging Sales in and applying a Quick Table Calculation for the percent of total sales, it would instead calculate the sub-category percent of total sales, not the category-level percent. That's why different sub-categories like Binders and Appliances have different values for this measure. To recap, Level of Detail or LOD calculations let you calculate at a different level than what's shown in your chart. The three types include FIXED, which locks the calculation at a specific level, INCLUDE, which adds more details to your calculation, and EXCLUDE, which removes details to focus on a broader level. LODs let us answer questions that regular calculations can't, like comparing different levels of detail within the same view.

Frequently asked questions

Why does FIXED Category Sales show the same number when a Sub-Category is filtered out?

FIXED expressions are evaluated before standard dimension filters in Tableau's order of operations, rendering them immune to chart-level filter exclusions.

What is the mathematical difference between AVG(Sales) and an INCLUDE AVG(Sales) LOD?

Standard AVG(Sales) averages every underlying raw transaction row, whereas INCLUDE averages the sales per specified dimension first and then averages those group results.

When should I use EXCLUDE instead of FIXED?

Use EXCLUDE when you want to remove a specific dimension from the calculation while ensuring the computed total still reacts dynamically to other active sheet filters.

How was this lesson?

Your feedback helps us refine explanations and catch bugs.