EXCLUDE LOD expression in Tableau - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants to see the average sales per customer, excluding the effect of product categories, to understand customer spending behavior better.

📊 Data: You have sales transaction data including Customer ID, Product Category, Sales Amount, and Order Date.

🎯 Deliverable: Create a Tableau dashboard showing average sales per customer excluding product category detail using EXCLUDE LOD expression.

Sample Data

Customer ID	Product Category	Sales Amount	Order Date
C001	Electronics	200	2024-01-10
C001	Clothing	150	2024-01-15
C002	Electronics	300	2024-01-12
C002	Clothing	100	2024-01-20
C003	Electronics	250	2024-01-18
C003	Clothing	200	2024-01-22
C004	Electronics	400	2024-01-25
C004	Clothing	300	2024-01-28

1

Step 1: Connect your sales data to Tableau and load the data source.

No formula needed.

Expected Result

Data source with Customer ID, Product Category, Sales Amount, and Order Date is ready in Tableau.

2

Step 2: Create a calculated field to compute total sales per customer excluding product category detail using EXCLUDE LOD expression.

Name: Total Sales per Customer Excluding Category Formula: {EXCLUDE [Product Category] : SUM([Sales Amount])}

Expected Result

Calculated field that sums sales per customer ignoring product category.

3

Step 3: Create another calculated field to count distinct customers.

Name: Distinct Customers Formula: COUNTD([Customer ID])

Expected Result

Calculated field that counts unique customers.

4

Step 4: Create a calculated field for average sales per customer excluding product category.

Name: Avg Sales per Customer Excl Category Formula: SUM([Sales Amount]) / COUNTD([Customer ID])

Expected Result

Calculated field showing average sales per customer ignoring product category.

5

Step 5: Build a view: Drag Customer ID to Rows, and the 'Total Sales per Customer Excluding Category' calculated field to Text on Marks card.

No formula needed.

Expected Result

Table showing total sales per customer ignoring product category.

6

Step 6: Create a dashboard and add the view. Add a title 'Average Sales per Customer Excluding Product Category'.

No formula needed.

Expected Result

Dashboard displays average sales per customer ignoring product category detail.

Final Result

Dashboard: Average Sales per Customer Excluding Product Category

+-------------+-----------------------------+
| Customer ID | Total Sales Excluding Category |
+-------------+-----------------------------+
| C001        | 350                         |
| C002        | 400                         |
| C003        | 450                         |
| C004        | 700                         |
+-------------+-----------------------------+

Average Sales per Customer (Excl Category): 475

✓When ignoring product categories, average sales per customer is $475.

✓Customer C004 spends the most overall, regardless of product category.

✓EXCLUDE LOD expression helps analyze customer spending without product category influence.

Bonus Challenge

Create a similar EXCLUDE LOD calculation to find average sales per product category excluding customer detail.

Show Hint

Use {EXCLUDE [Customer ID] : SUM([Sales Amount])} to ignore customer level detail.