FIXED LOD expression in Tableau - Dashboard Guide

Dashboard Mode - FIXED LOD expression

Business Question

How can we calculate the total sales per region regardless of other filters, and compare it with the filtered sales?

Sample Data

Order ID	Region	Category	Sales
1001	East	Furniture	200
1002	West	Technology	450
1003	East	Office Supplies	150
1004	South	Furniture	300
1005	West	Office Supplies	100
1006	South	Technology	500
1007	East	Technology	350

Dashboard Components

• KPI Card: Total Sales (Filtered)
  Shows the sum of sales based on current filters.
  Formula: SUM([Sales])
  Example Result: If Region filter is East, result is 700 (200+150+350)
• KPI Card: Total Sales per Region (Fixed LOD)
  Shows total sales per region ignoring other filters.
  Formula: {FIXED [Region]: SUM([Sales])}
  Example Result: For East region, result is 700; for West, 550; for South, 800
• Bar Chart: Sales by Category
  Displays sales sum by category, affected by filters.
  Formula: SUM([Sales]) grouped by [Category]
• Table: Sales Details
  Shows detailed sales data rows, filtered by user selections.
  Columns: Order ID, Region, Category, Sales

Dashboard Layout

+----------------------+--------------------------+
| Total Sales (Filtered) | Total Sales per Region    |
|        [700]          | (Fixed LOD) [700 East]    |
+----------------------+--------------------------+
|                          Bar Chart: Sales by Category           |
|               (Furniture, Technology, Office Supplies)          |
+---------------------------------------------------------------+
|                          Table: Sales Details                   |
|               (Order ID, Region, Category, Sales)              |
+---------------------------------------------------------------+

Interactivity

User can select Region or Category filters.

• Total Sales (Filtered) updates based on filters.
• Bar Chart updates to show sales by category for filtered data.
• Sales Details Table shows only rows matching filters.
• Total Sales per Region (Fixed LOD) remains fixed per region ignoring other filters except Region filter itself (if applied).

Self Check

If you add a filter for Region = West, which components update and what are their new values?

• Total Sales (Filtered): Updates to 550 (450 + 100)
• Bar Chart: Shows sales by category for West region only.
• Sales Details Table: Shows only orders 1002 and 1005.
• Total Sales per Region (Fixed LOD): Shows 550 for West region (fixed total sales for West).

Key Result

Dashboard shows total sales filtered vs fixed per region, sales by category, and detailed sales data.

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