INCLUDE 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 product category within each region, even if some products have few sales records. This helps understand category performance in each region more clearly.

📊 Data: You have sales data with columns: Region, Product Category, Product Name, and Sales Amount. Each row is a sale of a product.

🎯 Deliverable: Create a Tableau dashboard showing average sales per product category by region using an INCLUDE LOD expression.

Sample Data

Region	Product Category	Product Name	Sales Amount
North	Electronics	Smartphone	500
North	Electronics	Laptop	1200
North	Furniture	Desk	300
South	Electronics	Smartphone	700
South	Furniture	Chair	150
South	Furniture	Desk	400
East	Electronics	Tablet	350
East	Furniture	Chair	200
East	Furniture	Desk	450
West	Electronics	Laptop	1100
West	Furniture	Desk	500
West	Furniture	Chair	250

1

Step 1: Connect your sales data to Tableau and load it into the workspace.

No formula needed; just import the data.

Expected Result

Data table with columns Region, Product Category, Product Name, Sales Amount is available in Tableau.

2

Step 2: Create a calculated field to compute average sales per product within each category and region using INCLUDE LOD expression.

Name: Avg Sales per Product Formula: {INCLUDE [Product Name] : AVG([Sales Amount])}

Expected Result

A new calculated field 'Avg Sales per Product' that calculates average sales per product considering product name granularity.

3

Step 3: Build a view with Rows as Region, Columns as Product Category, and Values as the average of 'Avg Sales per Product'.

Drag Region to Rows, Product Category to Columns, and drag 'Avg Sales per Product' to Text or Label, then set aggregation to AVG.

Expected Result

A table showing average sales per product category for each region.

4

Step 4: Format the view to improve readability: add color shading to highlight higher average sales.

Use Color Marks card with a gradient from light to dark based on average sales values.

Expected Result

The table visually highlights categories with higher average sales in each region.

5

Step 5: Create a dashboard and add the view with filters for Region and Product Category for interactivity.

Add filter controls for Region and Product Category to the dashboard.

Expected Result

Interactive dashboard allowing manager to explore average sales per category by region.

Final Result

Dashboard: Average Sales per Product Category by Region

+---------+--------------+--------------+
| Region  | Electronics  | Furniture    |
+---------+--------------+--------------+
| North   | 850          | 300          |
| South   | 700          | 275          |
| East    | 350          | 325          |
| West    | 1100         | 375          |
+---------+--------------+--------------+

Color shading: Darker cells mean higher average sales.

✓West region has the highest average sales in Electronics category (1100).

✓Furniture category average sales are fairly consistent across regions, around 300-375.

✓North region shows strong Electronics sales driven by Laptop and Smartphone.

✓INCLUDE LOD expression helps capture average sales per product even when data is aggregated by category and region.

Bonus Challenge

Modify the dashboard to also show total sales per region alongside average sales per category using a combined view.

Show Hint

Create another calculated field for total sales per region using FIXED LOD expression: {FIXED [Region] : SUM([Sales Amount])} and add it to the dashboard.

Practice

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

easy

A. Changes the data source connection

B. Removes all filters from the calculation

C. Aggregates data only at the highest level of detail

D. Calculates a measure at a more detailed level than the current view

Solution

1. Step 1: Understand the purpose of INCLUDE LOD

   INCLUDE LOD lets you add extra detail to calculations without changing the main view's level of detail.

2. Step 2: Compare options to definition

   Calculates a measure at a more detailed level than the current view correctly states it calculates measures at a more detailed level than the current view, which matches the INCLUDE LOD purpose.

3. Final Answer:

   Calculates a measure at a more detailed level than the current view -> Option D

4. Quick Check:

   INCLUDE LOD = More detailed calculation [OK]

Hint: INCLUDE adds detail inside calculation without changing view [OK]

Common Mistakes:

• Confusing INCLUDE with EXCLUDE or FIXED LOD
• Thinking it removes filters
• Assuming it changes data source

2. Which of the following is the correct syntax for an INCLUDE LOD expression to calculate average sales per customer in Tableau?

easy

A. { INCLUDE [Customer ID] : AVG([Sales]) }

B. { EXCLUDE [Customer ID] : AVG([Sales]) }

C. INCLUDE [Customer ID] AVG([Sales])

D. { FIXED [Customer ID] AVG([Sales]) }

Solution

1. Step 1: Recall correct INCLUDE LOD syntax

   The correct syntax uses curly braces, INCLUDE keyword, dimension in brackets, colon, then aggregation function.

2. Step 2: Check each option

   { INCLUDE [Customer ID] : AVG([Sales]) } matches syntax: { INCLUDE [Customer ID] : AVG([Sales]) }. Others have wrong keywords, missing braces, or missing colon.

3. Final Answer:

   { INCLUDE [Customer ID] : AVG([Sales]) } -> Option A

4. Quick Check:

   Syntax = { INCLUDE [Dimension] : Aggregation } [OK]

Hint: INCLUDE syntax always uses braces and colon [OK]

Common Mistakes:

• Omitting curly braces or colon
• Using wrong LOD keyword (EXCLUDE or FIXED)
• Missing brackets around dimension

3. Given the data view shows total sales by Region, what will the following INCLUDE LOD expression calculate?

{ INCLUDE [Customer ID] : SUM([Sales]) }

Options:

medium

A. Sum of sales per customer then aggregated by region

B. Average sales per region

C. Sum of sales per region ignoring customers

D. Sum of sales ignoring region and customer

Solution

1. Step 1: Understand the view and LOD expression

   The view is grouped by Region. The INCLUDE LOD adds Customer ID detail inside the calculation.

2. Step 2: Interpret the calculation result

   The expression sums sales per Customer ID first, then aggregates these sums by Region in the view.

3. Final Answer:

   Sum of sales per customer then aggregated by region -> Option A

4. Quick Check:

   INCLUDE adds detail level inside aggregation [OK]

Hint: INCLUDE adds detail inside aggregation, then aggregates by view [OK]

Common Mistakes:

• Thinking it ignores customers
• Confusing sum with average
• Assuming it ignores region

4. Identify the error in this INCLUDE LOD expression:

{ INCLUDE Customer ID : SUM([Sales]) }

medium

A. Incorrect use of curly braces

B. Missing aggregation function

C. Missing square brackets around Customer ID

D. INCLUDE keyword should be EXCLUDE

Solution

1. Step 1: Check syntax for dimension references

   Dimension names must be enclosed in square brackets in LOD expressions.

2. Step 2: Identify missing brackets

   Customer ID is missing square brackets, causing syntax error.

3. Final Answer:

   Missing square brackets around Customer ID -> Option C

4. Quick Check:

   Dimensions need [brackets] in LOD [OK]

Hint: Always put dimension names in square brackets [OK]

Common Mistakes:

• Omitting brackets around dimensions
• Confusing INCLUDE with EXCLUDE
• Missing curly braces

5. You want to calculate the average sales per product category but also include the customer-level detail in the calculation without changing the view's level of detail. Which INCLUDE LOD expression should you use?

hard

A. AVG([Sales])

B. { INCLUDE [Customer ID] : AVG([Sales]) }

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

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

Solution

1. Step 1: Understand the requirement

   You want average sales per product category but with customer-level detail included in calculation without changing view level.

2. Step 2: Choose correct LOD expression

   INCLUDE adds customer detail inside calculation while keeping view level (product category). { INCLUDE [Customer ID] : AVG([Sales]) } matches this need.

3. Final Answer:

   { INCLUDE [Customer ID] : AVG([Sales]) } -> Option B

4. Quick Check:

   INCLUDE adds detail without changing view [OK]

Hint: Use INCLUDE to add customer detail inside product category calc [OK]

Common Mistakes:

• Using FIXED which ignores view level
• Using EXCLUDE which removes detail
• Using simple AVG ignoring detail