Why LOD expressions control aggregation scope in Tableau - Business Case Study

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to understand total sales per customer regardless of the product category, even though the report shows sales broken down by category.

📊 Data: You have sales data with columns: Customer ID, Product Category, Sales Amount.

🎯 Deliverable: Create a Tableau report that shows sales by product category and also the total sales per customer using LOD expressions to control aggregation.

Sample Data

Customer ID	Product Category	Sales Amount
C001	Electronics	200
C001	Clothing	150
C002	Electronics	300
C002	Clothing	100
C003	Electronics	400
C003	Clothing	200
C003	Home	100

1

Step 1: Connect your sales data to Tableau and open a new worksheet.

No formula needed.

Expected Result

Data is loaded and ready for analysis.

2

Step 2: Drag 'Product Category' to Rows and 'Sales Amount' to Columns to see sales by category.

Aggregation: SUM([Sales Amount])

Expected Result

Bar chart showing total sales for each product category.

3

Step 3: Drag 'Customer ID' to Rows next to 'Product Category' to break down sales by customer and category.

No formula needed.

Expected Result

Table showing sales amounts for each customer within each product category.

4

Step 4: Create a calculated field named 'Total Sales per Customer' using an LOD expression to fix aggregation at the customer level.

{FIXED [Customer ID] : SUM([Sales Amount])}

Expected Result

A new measure that shows total sales per customer regardless of product category.

5

Step 5: Drag the 'Total Sales per Customer' calculated field to the view next to sales by category.

No formula needed.

Expected Result

The view shows sales by product category and the total sales per customer repeated for each category.

6

Step 6: Explain that the LOD expression controls aggregation scope by fixing the sum at the customer level, ignoring the product category breakdown.

No formula needed.

Expected Result

Clear understanding that LOD expressions let you calculate totals at a different level than the view's detail.

Final Result

Product Category | Customer ID | Sales Amount | Total Sales per Customer
---------------------------------------------------------------
Electronics      | C001        | 200          | 350
Clothing         | C001        | 150          | 350
Electronics      | C002        | 300          | 400
Clothing         | C002        | 100          | 400
Electronics      | C003        | 400          | 700
Clothing         | C003        | 200          | 700
Home             | C003        | 100          | 700

✓Sales amounts are broken down by product category and customer.

✓Total sales per customer are the same across all categories for that customer.

✓LOD expression fixes aggregation at customer level, ignoring category detail.

✓This helps compare category sales to overall customer sales easily.

Bonus Challenge

Create another LOD expression to calculate total sales per product category regardless of customer.

Show Hint

Use {FIXED [Product Category] : SUM([Sales Amount])} to fix aggregation at the category level.

Practice

1. What is the main purpose of Level of Detail (LOD) expressions in Tableau?

easy

A. To export data to Excel automatically

B. To change the color of charts dynamically

C. To filter data based on user input

D. To control how data is grouped before aggregation

Solution

1. Step 1: Understand LOD expression role

   LOD expressions let you fix or change the grouping level before aggregation happens.

2. Step 2: Compare with other options

   Changing colors, filtering, or exporting are unrelated to aggregation scope control.

3. Final Answer:

   To control how data is grouped before aggregation -> Option D

4. Quick Check:

   LOD controls grouping before aggregation = A [OK]

Hint: LOD fixes grouping level before aggregation [OK]

Common Mistakes:

• Confusing LOD with filtering
• Thinking LOD changes chart colors
• Assuming LOD exports data

2. Which of the following is the correct syntax for a FIXED LOD expression in Tableau?

easy

A. { FIXED [Region] : SUM([Sales]) }

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

C. { EXCLUDE [Region] : AVG([Sales]) }

D. { FIXED SUM([Sales]) : [Region] }

Solution

1. Step 1: Identify correct FIXED syntax

   FIXED LOD syntax is { FIXED [Dimension] : Aggregation }, so { FIXED [Region] : SUM([Sales]) } matches.

2. Step 2: Check other options

   Options A and B use different LOD types, and D has incorrect order.

3. Final Answer:

   { FIXED [Region] : SUM([Sales]) } -> Option A

4. Quick Check:

   FIXED syntax = { FIXED [Dimension] : Aggregation } [OK]

Hint: FIXED syntax: { FIXED [Dimension] : Aggregation } [OK]

Common Mistakes:

• Swapping dimension and aggregation order
• Using INCLUDE or EXCLUDE instead of FIXED
• Missing curly braces

3. Given the data view grouped by [Category], what will { FIXED [Category] : SUM([Sales]) } return compared to SUM([Sales])?

medium

A. FIXED returns total sales ignoring Category grouping

B. SUM returns total sales ignoring Category grouping

C. Both return the same total sales per category

D. FIXED returns average sales per category

Solution

1. Step 1: Understand FIXED with grouping

   FIXED on [Category] fixes aggregation at category level, matching the view's grouping.

2. Step 2: Compare with SUM aggregation

   SUM([Sales]) aggregated by Category in the view also sums sales per category.

3. Final Answer:

   Both return the same total sales per category -> Option C

4. Quick Check:

   FIXED on grouping dimension = same as SUM by that group [OK]

Hint: FIXED on grouped dimension equals normal aggregation [OK]

Common Mistakes:

• Thinking FIXED ignores grouping always
• Confusing FIXED with INCLUDE or EXCLUDE
• Assuming FIXED changes aggregation type

4. Identify the error in this LOD expression: { FIXED : SUM([Sales]) }

medium

A. Missing dimension after FIXED keyword

B. SUM aggregation is not allowed in LOD

C. Curly braces are not needed

D. FIXED cannot be used without INCLUDE or EXCLUDE

Solution

1. Step 1: Check FIXED syntax requirements

   FIXED requires at least one dimension to fix aggregation scope, missing here.

2. Step 2: Validate other parts

   SUM is valid aggregation, curly braces are required, and FIXED can be used alone.

3. Final Answer:

   Missing dimension after FIXED keyword -> Option A

4. Quick Check:

   FIXED needs dimension(s) specified [OK]

Hint: FIXED must specify dimension(s) after keyword [OK]

Common Mistakes:

• Leaving out dimension after FIXED
• Removing curly braces
• Confusing FIXED with INCLUDE/EXCLUDE requirement

5. You want to calculate the total sales per customer ignoring the current view's grouping by product. Which LOD expression should you use?

hard

A. { INCLUDE [Product] : SUM([Sales]) }

B. { FIXED [Customer] : SUM([Sales]) }

C. { EXCLUDE [Customer] : SUM([Sales]) }

D. { FIXED [Product] : SUM([Sales]) }

Solution

1. Step 1: Understand requirement

   You want total sales per customer ignoring product grouping in the view.

2. Step 2: Choose correct LOD type

   FIXED on [Customer] fixes aggregation at customer level, ignoring other groupings like product.

3. Step 3: Evaluate options

   INCLUDE adds dimensions, EXCLUDE removes dimensions incorrectly here, FIXED on product fixes wrong dimension.

4. Final Answer:

   { FIXED [Customer] : SUM([Sales]) } -> Option B

5. Quick Check:

   FIXED fixes aggregation ignoring view grouping [OK]

Hint: Use FIXED on dimension to ignore view grouping [OK]

Common Mistakes:

• Using INCLUDE or EXCLUDE instead of FIXED
• Fixing wrong dimension
• Confusing FIXED with filtering