EXCLUDE LOD expression in Tableau - Step-by-Step Guide

Introduction

EXCLUDE LOD expressions let you remove certain dimensions from your view when calculating values. This helps you see data summaries without some details, like totals ignoring a category.

When you want to calculate total sales ignoring the product category but keeping other filters.

When your dashboard needs to show average sales per region, ignoring the individual stores.

When you want to compare overall performance without breaking down by month or day.

When you want to remove a dimension temporarily to see a simpler summary.

When you want to create a calculation that ignores a specific filter or grouping.

Steps

Step 1: Open

- Tableau Desktop and load your data source

Your data appears in the Data pane

Step 2: Click

- Analysis menu > Create Calculated Field

A dialog box opens to enter a new calculation

Step 3: Type

- Calculation editor

You can write your EXCLUDE LOD expression

💡 Start with curly braces { } and use EXCLUDE keyword

Step 4: Enter

- Calculation editor

Example: {EXCLUDE [Category] : SUM([Sales])} calculates total sales ignoring Category

💡 Replace [Category] with the dimension you want to exclude

Step 5: Click

- OK button in the calculation editor

The new calculated field appears in the Data pane

Step 6: Drag

- New calculated field to Rows or Columns shelf

The view updates showing the calculation excluding the chosen dimension

Before vs After

Before

View shows sales broken down by Region and Category with SUM([Sales])

After

View shows sales broken down by Region only, with {EXCLUDE [Category] : SUM([Sales])} calculation ignoring Category

Settings Reference

Calculation editor

📍 Analysis menu > Create Calculated Field

To create custom calculations like EXCLUDE LOD expressions

Default: Empty

Data pane

📍 Left side panel in Tableau Desktop

To organize and access fields for building views and calculations

Default: Shows all fields from data source

Common Mistakes

Writing EXCLUDE LOD without curly braces

Tableau requires LOD expressions to be inside curly braces to work

Always write EXCLUDE expressions like {EXCLUDE [Dimension] : AGGREGATION([Measure])}

Excluding a dimension not in the view

EXCLUDE only removes dimensions present in the view; excluding others has no effect

Make sure the dimension you exclude is part of the current view or visualization

Summary

EXCLUDE LOD expressions remove specific dimensions from calculations to simplify data views.

They help create summaries ignoring certain details without changing the whole view.

Always use curly braces and ensure the excluded dimension is in the view for correct results.

Practice

1. What does the EXCLUDE LOD expression do in Tableau?

easy

A. It filters data based on a condition.

B. It adds extra dimensions to the view for detailed analysis.

C. It removes specified dimensions from the view to aggregate data at a higher level.

D. It creates a calculated field that sums all values.

Solution

1. Step 1: Understand the purpose of EXCLUDE LOD

   EXCLUDE LOD removes certain dimensions from the calculation, simplifying the aggregation.

2. Step 2: Compare with other options

   Adding dimensions or filtering is not what EXCLUDE does; it specifically ignores dimensions.

3. Final Answer:

   It removes specified dimensions from the view to aggregate data at a higher level. -> Option C

4. Quick Check:

   EXCLUDE = remove dimensions [OK]

Hint: EXCLUDE means ignore some dimensions to see bigger picture [OK]

Common Mistakes:

• Confusing EXCLUDE with INCLUDE or FIXED LOD
• Thinking EXCLUDE filters data instead of changing aggregation level
• Assuming EXCLUDE adds dimensions instead of removing

2. Which of the following is the correct syntax for an EXCLUDE LOD expression in Tableau?

easy

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

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

C. {INCLUDE [Category] : SUM([Sales])}

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

Solution

1. Step 1: Recall the correct LOD syntax

   The correct syntax is curly braces with EXCLUDE, then the dimension in brackets, colon, and aggregation.

2. Step 2: Check each option

   {EXCLUDE [Category] : SUM([Sales])} matches the correct syntax. Options A, B, and C have syntax errors or wrong order.

3. Final Answer:

   {EXCLUDE [Category] : SUM([Sales])} -> Option A

4. Quick Check:

   LOD syntax = {EXCLUDE [Dimension] : Aggregation} [OK]

Hint: LOD expressions always use curly braces and colon [OK]

Common Mistakes:

• Placing EXCLUDE outside curly braces
• Swapping INCLUDE and EXCLUDE keywords
• Incorrect order of aggregation and dimensions

3. Given the view shows sales by [Region] and [Category], what does this expression return?
{EXCLUDE [Category] : SUM([Sales])}

medium

A. Total sales for each Region ignoring Category breakdown

B. Total sales for each Category ignoring Region breakdown

C. Total sales for each Region and Category combined

D. Total sales for all data ignoring Region and Category

Solution

1. Step 1: Identify dimensions in the view

   The view has Region and Category dimensions.

2. Step 2: Understand EXCLUDE [Category]

   EXCLUDE removes Category from calculation, so aggregation is by Region only.

3. Final Answer:

   Total sales for each Region ignoring Category breakdown -> Option A

4. Quick Check:

   EXCLUDE Category = aggregate by Region only [OK]

Hint: EXCLUDE removes dimension from aggregation level [OK]

Common Mistakes:

• Thinking EXCLUDE filters data instead of changing aggregation
• Confusing which dimension is excluded
• Assuming it sums all data ignoring all dimensions

4. You wrote this EXCLUDE LOD expression:
{EXCLUDE [Region] : SUM([Profit])}
But Tableau shows an error. What is the likely cause?

medium

A. You forgot to include curly braces around the expression.

B. You cannot exclude a dimension not present in the view.

C. The syntax is correct; the error is due to missing data.

D. You must use INCLUDE instead of EXCLUDE for aggregation.

Solution

1. Step 1: Check the syntax of the expression

   The syntax is correct, including curly braces.

2. Step 2: Identify the error cause

   EXCLUDE LOD can only exclude dimensions present in the view; if [Region] is absent, it errors.

3. Final Answer:

   You cannot exclude a dimension not present in the view. -> Option B

4. Quick Check:

   EXCLUDE requires dimension in view [OK]

Hint: EXCLUDE only for dimensions in the view [OK]

Common Mistakes:

• Assuming curly braces are missing
• Confusing EXCLUDE with INCLUDE
• Overlooking that excluded dimension must be in the view

5. You have sales data by [Region], [Category], and [Sub-Category]. You want to see total sales by Region only, ignoring Category and Sub-Category. Which EXCLUDE LOD expression achieves this?

hard

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

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

C. {EXCLUDE [Sub-Category] : SUM([Sales])}

D. {EXCLUDE [Category], [Sub-Category] : SUM([Sales])}

Solution

1. Step 1: Identify dimensions to exclude

   You want to ignore both Category and Sub-Category to aggregate by Region only.

2. Step 2: Write EXCLUDE with both dimensions

   Use EXCLUDE with both [Category] and [Sub-Category] inside curly braces.

3. Final Answer:

   {EXCLUDE [Category], [Sub-Category] : SUM([Sales])} -> Option D

4. Quick Check:

   Exclude both dimensions to aggregate by Region [OK]

Hint: List all dimensions to exclude inside EXCLUDE brackets [OK]

Common Mistakes:

• Excluding only one dimension when two are needed
• Excluding Region instead of Category/Sub-Category
• Incorrect syntax with missing commas