FIXED 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 total sales per product category regardless of the filters applied on the sales region or date.

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

🎯 Deliverable: Create a Tableau dashboard showing total sales by product category using a FIXED LOD expression that ignores filters on region and date.

Sample Data

Order ID	Product Category	Sales Region	Order Date	Sales Amount
1001	Electronics	East	2024-01-05	500
1002	Furniture	West	2024-01-07	700
1003	Electronics	East	2024-02-10	300
1004	Office Supplies	South	2024-02-15	200
1005	Furniture	East	2024-03-01	400
1006	Electronics	West	2024-03-05	600
1007	Office Supplies	North	2024-03-10	150
1008	Furniture	South	2024-03-15	350
1009	Electronics	North	2024-04-01	450
1010	Office Supplies	West	2024-04-05	300

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: Create a calculated field named 'Total Sales by Category Fixed' using a FIXED LOD expression to calculate total sales per product category ignoring filters on region and date.

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

Expected Result

The calculated field returns total sales per product category regardless of filters.

3

Step 3: Drag 'Product Category' to Rows shelf.

No formula needed.

Expected Result

Rows show each product category.

4

Step 4: Drag the calculated field 'Total Sales by Category Fixed' to Columns shelf.

No formula needed.

Expected Result

Columns show total sales per product category ignoring filters.

5

Step 5: Add filters for 'Sales Region' and 'Order Date' to the worksheet and select any subset of regions and dates.

No formula needed.

Expected Result

Filters apply but the total sales per category remain constant.

6

Step 6: Create a dashboard and add this worksheet with filters for region and date.

No formula needed.

Expected Result

Dashboard shows total sales by product category fixed, unaffected by region or date filters.

Final Result

Product Category | Total Sales by Category Fixed
---------------------------------------------
Electronics      | 1850
Furniture        | 1450
Office Supplies  | 650

✓Electronics is the highest-selling product category with total sales of 1850.

✓Furniture follows with total sales of 1450.

✓Office Supplies have total sales of 650.

✓These totals remain the same even when filtering by sales region or order date.

Bonus Challenge

Modify the FIXED LOD expression to calculate total sales per product category and sales region, ignoring only the order date filter.

Show Hint

Use {FIXED [Product Category], [Sales Region]: SUM([Sales Amount])} and apply filters only on Order Date.

Practice

1. What does a FIXED LOD expression do in Tableau?

easy

A. Calculates a value at a specific dimension level, ignoring other filters

B. Filters data dynamically based on user selection

C. Aggregates data only after all filters are applied

D. Creates a new calculated field without aggregation

Solution

1. Step 1: Understand FIXED LOD purpose

   FIXED LOD calculates values at a fixed dimension level regardless of other filters.

2. Step 2: Compare with other options

   Dynamic filtering based on user selection and aggregating data after all filters do not describe FIXED LOD. Creating a new calculated field without aggregation is unrelated.

3. Final Answer:

   Calculates a value at a specific dimension level, ignoring other filters -> Option A

4. Quick Check:

   FIXED LOD = fixed level calculation [OK]

Hint: FIXED means fixed level, ignoring filters [OK]

Common Mistakes:

• Confusing FIXED with INCLUDE or EXCLUDE LOD
• Thinking FIXED respects all filters
• Assuming FIXED creates dynamic filters

2. Which of the following is the correct syntax for a FIXED LOD expression to calculate total sales by Region?

easy

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

B. FIXED {SUM([Sales]) BY [Region]}

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

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

Solution

1. Step 1: Recall correct FIXED LOD syntax

   The correct syntax is {FIXED [Dimension]: AGG([Measure])}.

2. Step 2: Check each option

   {FIXED [Region]: SUM([Sales])} matches syntax exactly. The other options have invalid syntax like misplaced braces, missing colons, or use INCLUDE instead of FIXED.

3. Final Answer:

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

4. Quick Check:

   Correct FIXED syntax = {FIXED [Region]: SUM([Sales])} [OK]

Hint: FIXED syntax: {FIXED [Dimension]: AGG([Measure])} [OK]

Common Mistakes:

• Using INCLUDE instead of FIXED
• Misplacing braces or colons
• Omitting aggregation function

3. Given the data below, what is the result of {FIXED [Category]: SUM([Sales])} when placed on a view filtered to only show 'Furniture' products?

Data:
- Category: Furniture, Office Supplies
- Sales: Furniture = 1000, Office Supplies = 500

Options:

medium

A. 0

B. 1500

C. 500

D. 1000

Solution

1. Step 1: Understand FIXED LOD computes before viz filters

   {FIXED [Category]: SUM([Sales])} computes SUM([Sales]) partitioned by [Category] using full dataset before the Category filter.

2. Step 2: Calculate for Furniture

   Furniture total sales = 1000. Filter shows only Furniture (assuming Category on view), displaying 1000.

3. Final Answer:

   1000 -> Option D

4. Quick Check:

   FIXED [Category] = per-Category total for shown Category [OK]

Hint: FIXED [dim]: AGG = per-dim total before filter [OK]

Common Mistakes:

• Thinking FIXED ignores partition dim (picking grand total 1500)
• Confusing with {FIXED : SUM([Sales])} grand total
• Confusing FIXED with INCLUDE

4. Identify the error in this FIXED LOD expression:

{FIXED [Region] SUM([Profit])}

medium

A. Missing colon ':' after [Region]

B. SUM cannot be used inside FIXED

C. Braces {} are not allowed in FIXED expressions

D. Region should be inside quotes

Solution

1. Step 1: Check FIXED LOD syntax

   Correct syntax requires a colon ':' after the dimension list inside braces.

2. Step 2: Identify missing colon

   The expression misses ':' after [Region], causing syntax error.

3. Final Answer:

   Missing colon ':' after [Region] -> Option A

4. Quick Check:

   Colon required after dimension list [OK]

Hint: Always put ':' after dimensions in FIXED [OK]

Common Mistakes:

• Omitting colon ':' after dimension
• Misusing aggregation functions
• Incorrect brace usage

5. You want to create a dashboard showing each Product's sales as a percentage of total sales across all Regions, ignoring any Region filters applied. Which FIXED LOD expression correctly calculates total sales ignoring Region filters?

hard

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

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

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

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

Solution

1. Step 1: Understand requirement to ignore Region filters

   To ignore Region filters, FIXED expression should not include Region dimension.

2. Step 2: Analyze each option

   {FIXED : SUM([Sales])} fixes calculation at overall level (no dimension), ignoring Region filters. {FIXED [Region]: SUM([Sales])} fixes by Region, so respects Region. Options A and D use INCLUDE and EXCLUDE, not ignoring Region filters fully.

3. Final Answer:

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

4. Quick Check:

   FIXED without dimension ignores all filters [OK]

Hint: FIXED with no dimension ignores all filters [OK]

Common Mistakes:

• Including Region in FIXED when wanting to ignore it
• Using INCLUDE or EXCLUDE instead of FIXED
• Assuming EXCLUDE ignores filters fully