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.