LOD vs table calculations in Tableau - Business Scenario Comparison

Scenario Mode

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

📋 Request: Your manager wants to understand how sales vary by product category and region, and also wants to see the percentage contribution of each product to its category's total sales.

📊 Data: You have sales data with columns: Date, Region, Product Category, Product Name, and Sales Amount.

🎯 Deliverable: Create a Tableau dashboard showing total sales by product category and region, and a table calculation showing each product's percentage of its category's sales.

Sample Data

Date	Region	Product Category	Product Name	Sales Amount
2024-01-05	North	Electronics	Smartphone	5000
2024-01-10	North	Electronics	Laptop	7000
2024-01-15	South	Electronics	Smartphone	3000
2024-01-20	South	Furniture	Desk	2000
2024-01-25	East	Furniture	Chair	1500
2024-01-30	East	Furniture	Desk	2500
2024-02-05	West	Electronics	Laptop	4000
2024-02-10	West	Furniture	Chair	1000
2024-02-15	North	Furniture	Desk	3000
2024-02-20	South	Electronics	Laptop	3500

1

Step 1: Connect the sales data to Tableau and create a new worksheet.

Load the data source with columns: Date, Region, Product Category, Product Name, Sales Amount.

Expected Result

Data is available in Tableau for analysis.

2

Step 2: Create a view showing total sales by Product Category and Region.

Drag 'Product Category' to Rows, 'Region' to Columns, and SUM(Sales Amount) to Text on Marks card.

Expected Result

A table showing total sales for each product category in each region.

3

Step 3: Create a Level of Detail (LOD) calculation to find total sales per Product Category across all regions.

{FIXED [Product Category]: SUM([Sales Amount])}

Expected Result

A new field that shows total sales per product category regardless of region.

4

Step 4: Add the LOD calculation to the view to compare category totals with regional sales.

Drag the LOD calculation to Detail or Tooltip on Marks card.

Expected Result

Tooltip shows total sales per product category for all regions.

5

Step 5: Create a table calculation to find each product's percentage contribution to its product category's sales within the current view.

Create a calculated field: SUM([Sales Amount]) / TOTAL(SUM([Sales Amount])) with addressing set to Product Name and partitioning by Product Category.

Expected Result

A percentage value showing each product's share of its category's sales.

6

Step 6: Add Product Name to Rows and the percentage calculation to Text to show product-level contribution.

Drag 'Product Name' to Rows below Product Category, drag percentage calculation to Text on Marks card, format as percentage.

Expected Result

A detailed table showing each product's sales and its percentage of the category total.

7

Step 7: Build a dashboard combining the category-region sales view and the product percentage contribution table.

Create a new dashboard, add both worksheets, arrange for clear comparison.

Expected Result

Dashboard shows total sales by category and region, plus product contribution percentages.

Final Result

--------------------------------------------------
| Product Category | North | South | East | West |
|------------------|-------|-------|------|------|
| Electronics      | 12000 | 6500  |      | 4000 |
| Furniture        | 3000  | 2000  | 4000 | 1000 |
--------------------------------------------------

Product Contribution (% of Category Sales):
Electronics:
- Smartphone: 36%
- Laptop: 64%

Furniture:
- Desk: 75%
- Chair: 25%

✓Electronics sales are highest in the North region.

✓Laptop sales contribute more than half of Electronics category sales.

✓Desk is the main product driving Furniture sales.

✓Table calculations help show product contribution within categories dynamically.

✓LOD expressions provide fixed category totals regardless of region filters.

Bonus Challenge

Create a parameter to let users select a region and dynamically update the product contribution percentages for that region only.

Show Hint

Use a parameter for region selection and modify the LOD calculation to include the selected region filter.

Practice

1. What is the main difference between LOD expressions and table calculations in Tableau?

easy

A. LOD expressions fix calculations at a specific data level, ignoring some filters, while table calculations work on the data shown in the current view.

B. LOD expressions only work on aggregated data, while table calculations only work on raw data.

C. LOD expressions are faster than table calculations in all cases.

D. Table calculations ignore filters, but LOD expressions depend on the table layout.

Solution

1. Step 1: Understand LOD expressions

   LOD expressions fix the calculation at a certain level of detail, ignoring some filters applied to the view.

2. Step 2: Understand table calculations

   Table calculations work on the data currently displayed in the view and depend on how the table is laid out.

3. Final Answer:

   LOD expressions fix calculations at a specific data level, ignoring some filters, while table calculations work on the data shown in the current view. -> Option A

4. Quick Check:

   LOD fixes level, table calculations depend on view [OK]

Hint: LOD fixes level, table calculations depend on view layout [OK]

Common Mistakes:

• Thinking table calculations ignore filters
• Believing LOD always works on raw data
• Assuming LOD is always faster

2. Which of the following is the correct syntax for a fixed LOD expression in Tableau to calculate total sales per region?

easy

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

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

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

D. SUM([Sales]) FIXED BY [Region]

Solution

1. Step 1: Recall fixed LOD syntax

   The fixed LOD syntax is { FIXED [Dimension] : Aggregation }.

2. Step 2: Match syntax to question

   { FIXED [Region] : SUM([Sales]) } matches the correct syntax for total sales per region.

3. Final Answer:

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

4. Quick Check:

   Fixed LOD uses FIXED keyword and curly braces [OK]

Hint: Fixed LOD always starts with { FIXED ... } [OK]

Common Mistakes:

• Using INCLUDE or EXCLUDE instead of FIXED
• Missing curly braces
• Incorrect keyword order

3. Given a view showing sales by category and sub-category, what will the following LOD expression return?
{ FIXED [Category] : SUM([Sales]) }

medium

A. Total sales for each sub-category ignoring category

B. Total sales for each category repeated for all sub-categories within it

C. Total sales for the entire dataset ignoring category and sub-category

D. Running total of sales by sub-category

Solution

1. Step 1: Understand FIXED by Category

   The expression fixes sales at the category level, ignoring sub-category detail.

2. Step 2: Effect on sub-category rows

   Each sub-category row will show the total sales of its parent category, repeated.

3. Final Answer:

   Total sales for each category repeated for all sub-categories within it -> Option B

4. Quick Check:

   Fixed LOD repeats category total per sub-category [OK]

Hint: Fixed LOD repeats fixed level value for lower detail rows [OK]

Common Mistakes:

• Thinking it sums only sub-category sales
• Confusing FIXED with INCLUDE or EXCLUDE
• Assuming running total behavior

4. You created a table calculation for running total of sales but it shows incorrect results. Which of the following is the most likely cause?

medium

A. The filter is applied before the LOD calculation.

B. The LOD expression used is FIXED instead of INCLUDE.

C. The data source is missing a join condition.

D. The table calculation is not set to compute using the correct dimension.

Solution

1. Step 1: Understand table calculation behavior

   Table calculations depend on the table layout and the dimension used for computation.

2. Step 2: Identify common error

   If the running total is wrong, often the compute using dimension is set incorrectly.

3. Final Answer:

   The table calculation is not set to compute using the correct dimension. -> Option D

4. Quick Check:

   Table calcs need correct compute using dimension [OK]

Hint: Check 'compute using' dimension for table calculations [OK]

Common Mistakes:

• Confusing LOD and table calculation errors
• Blaming data joins for table calc issues
• Ignoring table layout impact

5. You want to show the percent of total sales by region in a view that filters by year. Which approach is best to ensure the percent always reflects all years, ignoring the year filter?

hard

A. Use a FIXED LOD expression to calculate total sales by region ignoring the year filter, then divide sales by this fixed total.

B. Use a table calculation for percent of total sales by region, which automatically ignores filters.

C. Apply the year filter after creating a table calculation for percent of total sales.

D. Use an INCLUDE LOD expression including year to calculate sales.

Solution

1. Step 1: Understand filter impact on calculations

   Table calculations and INCLUDE LOD respect filters, so year filter affects them.

2. Step 2: Use FIXED LOD to ignore year filter

   FIXED LOD can ignore filters like year, fixing total sales by region across all years.

3. Step 3: Calculate percent of total

   Divide sales by the fixed total sales to get percent ignoring year filter.

4. Final Answer:

   Use a FIXED LOD expression to calculate total sales by region ignoring the year filter, then divide sales by this fixed total. -> Option A

5. Quick Check:

   FIXED LOD ignores filters, perfect for fixed percent totals [OK]

Hint: Use FIXED LOD to ignore filters for fixed percent totals [OK]

Common Mistakes:

• Using table calculations that respect filters
• Using INCLUDE LOD which respects filters
• Applying filters after calculations incorrectly