FIXED LOD Expressions in Tableau
Learn how FIXED Level of Detail expressions calculate measures at explicitly defined dimensions, independently of the dimensions displayed in a Tableau visualization. Explore syntax, multiple dimensions, practical business examples, filter behaviour and advanced calculations.
1. Introduction to FIXED LOD Expressions
FIXED is one of the three main Level of Detail (LOD) expressions available in Tableau. It allows users to calculate values at a specific level of detail by explicitly specifying the dimensions that should be used in the calculation.
Unlike ordinary aggregations that follow the dimensions in the current visualization, FIXED expressions calculate results using the dimensions defined inside the expression. Therefore, adding or removing dimensions from a worksheet does not automatically change the FIXED calculation level.
Key Characteristics of FIXED LOD
- Calculates values at explicitly specified dimensions.
- Works independently of dimensions displayed in the view.
- Supports one or multiple dimensions.
- Can use aggregation functions such as SUM, AVG, MIN and MAX.
- Useful for customer-level, product-level and order-level analysis.
- Can be affected by context filters and data source filtering.
2. Syntax of FIXED LOD Expressions
A FIXED expression uses curly braces, the FIXED keyword, one or more dimensions and an aggregation function.
General Syntax
{ FIXED [Dimension] : AGG([Measure]) }
Syntax with Multiple Dimensions
{ FIXED [Dimension 1], [Dimension 2] : AGG([Measure]) }
Example
{ FIXED [Customer Name] : SUM([Sales]) }
Understanding Each Component
| Component | Description |
|---|---|
| Curly braces { } | Define the LOD expression. |
| FIXED | Specifies the fixed calculation level. |
| [Dimension] | Defines the dimension used to group the calculation. |
| AGG() | Specifies the aggregation function. |
| [Measure] | Identifies the field being calculated. |
3. How Does FIXED LOD Work?
Consider a sales dataset containing customer names, regions, products and sales amounts. When Region is placed in a Tableau worksheet, Tableau normally calculates total sales for each region.
However, a business may want to calculate total sales for each customer even when Customer Name is not included in the view. A FIXED expression can perform this calculation.
FIXED Calculation Process
Example
{ FIXED [Customer Name] : SUM([Sales]) }
This expression calculates the sum of Sales for each Customer Name. The calculation remains at customer level even if the worksheet displays only Region or Product.
4. Sample Dataset for FIXED LOD
Use the following sample sales dataset to understand how FIXED expressions work. The dataset contains customer transactions across different regions and products.
| Order ID | Customer | Region | Product | Sales |
|---|---|---|---|---|
| 1001 | Omkar | East | Laptop | 10000 |
| 1002 | Omkar | East | Mobile | 15000 |
| 1003 | Rahul | West | Laptop | 20000 |
| 1004 | Rahul | West | Mobile | 10000 |
| 1005 | Priya | South | Laptop | 12000 |
| 1006 | Priya | South | Mobile | 8000 |
5. Customer-Level Sales Using FIXED
A common business requirement is to calculate the total sales generated by each customer. This calculation is useful for customer performance analysis and identifying high-value customers.
Calculated Field
{ FIXED [Customer] : SUM([Sales]) }
Explanation
The expression groups the dataset by Customer and calculates the total Sales for each customer. Other dimensions in the visualization do not change this calculation level.
Expected Result
| Customer | Total Sales |
|---|---|
| Omkar | 25000 |
| Rahul | 30000 |
| Priya | 20000 |
Steps to Create Customer-Level Sales
- Open Tableau Desktop or Tableau Public.
- Connect to the sales dataset.
- Open a new worksheet.
- Right-click inside the Data pane.
- Select Create Calculated Field.
- Enter the calculated field name Customer Total Sales.
- Enter the following formula:
{ FIXED [Customer] : SUM([Sales]) }
- Click Apply or OK.
- Drag Customer to Rows.
- Drag Customer Total Sales into the view.
- Verify the total sales generated by each customer.
6. Region-Level Sales Using FIXED
FIXED can also calculate sales totals for each region independently of the dimensions displayed in the worksheet.
Calculated Field
{ FIXED [Region] : SUM([Sales]) }
Expected Result
| Region | Regional Sales |
|---|---|
| East | 25000 |
| West | 30000 |
| South | 20000 |
Even if Product is added to the worksheet, the FIXED calculation continues to calculate the total sales at Region level.
Practical Example
{ FIXED [Region] : SUM([Sales]) }
Add Region and Product to the worksheet. The regional sales value can be displayed alongside each product mark belonging to that region.
7. FIXED with Multiple Dimensions
FIXED expressions can use multiple dimensions when a calculation must be performed at a more specific level of detail.
For example, a business may want to calculate sales separately for each combination of Region and Product.
Syntax
{ FIXED [Region], [Product] : SUM([Sales]) }
Explanation
Tableau groups records by both Region and Product and calculates total sales for every unique combination of these dimensions.
Expected Result
| Region | Product | Total Sales |
|---|---|---|
| East | Laptop | 10000 |
| East | Mobile | 15000 |
| West | Laptop | 20000 |
| West | Mobile | 10000 |
| South | Laptop | 12000 |
| South | Mobile | 8000 |
8. Order-Level Calculations Using FIXED
Order-level calculations are useful when a dataset contains multiple records associated with the same order. FIXED can calculate a value for each Order ID.
Calculated Field
{ FIXED [Order ID] : SUM([Sales]) }
This expression calculates the total sales for every individual order, even when the view displays other dimensions.
Example
| Order ID | Sales |
|---|---|
| 1001 | 10000 |
| 1002 | 15000 |
| 1003 | 20000 |
| 1004 | 10000 |
| 1005 | 12000 |
| 1006 | 8000 |
Business Applications
- Calculate order-level revenue.
- Identify high-value orders.
- Compare order values across customers.
- Analyze order-level business performance.
9. Aggregation Functions Inside FIXED
FIXED expressions support different aggregation functions. The function selected depends on the type of calculation required by the business.
| Aggregation | FIXED Expression | Purpose |
|---|---|---|
| SUM | { FIXED [Customer] : SUM([Sales]) } |
Total sales per customer. |
| AVG | { FIXED [Customer] : AVG([Sales]) } |
Average transaction sales per customer. |
| MIN | { FIXED [Customer] : MIN([Sales]) } |
Minimum transaction sales per customer. |
| MAX | { FIXED [Customer] : MAX([Sales]) } |
Maximum transaction sales per customer. |
| COUNT | { FIXED [Customer] : COUNT([Order ID]) } |
Count of non-null order IDs per customer. |
| COUNTD | { FIXED [Customer] : COUNTD([Order ID]) } |
Distinct order count per customer. |
Example: Average Sales per Customer
{ FIXED [Customer] : AVG([Sales]) }
This expression calculates the average sales transaction value for each customer.
Example: Distinct Orders per Customer
{ FIXED [Customer] : COUNTD([Order ID]) }
This expression counts the unique orders associated with each customer.
10. FIXED LOD and View Dimensions
One of the most important characteristics of FIXED expressions is that they do not automatically follow the dimensions in the worksheet.
Suppose a worksheet displays Region and Product. Tableau normally calculates Sales for every Region and Product combination. However, a FIXED expression that specifies only Region calculates the sales total at the regional level.
Ordinary Aggregation
SUM([Sales])
The result follows the dimensions in the view.
FIXED Aggregation
{ FIXED [Region] : SUM([Sales]) }
The result remains at Region level even when Product is also included in the visualization.
View-Level vs FIXED-Level Calculation
Ordinary SUM(Sales)
FIXED Region
Product does not change the FIXED calculation level.
11. FIXED LOD and Filter Behaviour
Tableau has an order of operations that determines when filters are applied. FIXED expressions are evaluated after extract, data source and context filters, but before ordinary dimension filters.
This behaviour is important because a regular dimension filter may hide some records from the visualization without changing a FIXED calculation that was already evaluated.
Example: Region Filter
Consider the following expression:
{ FIXED [Customer] : SUM([Sales]) }
Suppose a user applies a regular Region filter to display only the East region. The FIXED calculation can still include customer sales from other regions because ordinary dimension filters are evaluated after FIXED.
Using Context Filters
If the Region filter is converted into a context filter, Tableau applies it before evaluating the FIXED expression. The calculation then uses the records remaining after that context filter.
Simplified Filter Order
Simplified representation of the important filter stages relevant to FIXED LOD expressions.
How to Add a Context Filter
- Drag a dimension such as Region to the Filters shelf.
- Select the required region values.
- Click OK.
- Right-click the Region filter pill.
- Select Add to Context.
- The filter changes to a context filter.
- Review the FIXED LOD calculation again.
12. FIXED LOD with Date Fields
FIXED expressions can also calculate values at a specific date level. This is useful for monthly sales, yearly sales and customer purchase analysis.
Example: Monthly Sales
{ FIXED DATETRUNC('month', [Order Date]) : SUM([Sales]) }
This expression calculates total sales for each month based on the Order Date field.
Example: Yearly Sales
{ FIXED YEAR([Order Date]) : SUM([Sales]) }
This expression calculates total sales for each year.
Example: First Purchase Date per Customer
{ FIXED [Customer] : MIN([Order Date]) }
This expression finds the earliest order date for each customer. It can be useful when analyzing customer acquisition and purchase history.
13. Practical Business Example: Sales Performance Dashboard
A retail company wants to create a sales performance dashboard. The management team needs to analyze customer revenue, regional sales, product performance and order counts.
Calculation 1: Customer Revenue
{ FIXED [Customer] : SUM([Sales]) }
Calculates the total revenue generated by each customer.
Calculation 2: Regional Revenue
{ FIXED [Region] : SUM([Sales]) }
Calculates the total revenue generated by each region.
Calculation 3: Product Revenue
{ FIXED [Product] : SUM([Sales]) }
Calculates the total revenue generated by each product.
Calculation 4: Customer Order Count
{ FIXED [Customer] : COUNTD([Order ID]) }
Calculates the number of unique orders placed by each customer.
Calculation 5: First Customer Purchase
{ FIXED [Customer] : MIN([Order Date]) }
Identifies the earliest recorded purchase date for each customer.
Dashboard Creation Steps
- Connect Tableau to the sales dataset.
- Create the five calculated fields described above.
- Create a worksheet for customer revenue.
- Create another worksheet for regional revenue.
- Create a product performance worksheet.
- Create a worksheet showing unique customer orders.
- Create a worksheet for first purchase dates.
- Combine the worksheets into a dashboard.
- Add appropriate filters and dashboard actions.
- Validate the results against the source dataset.
Example Dashboard Metrics
Illustrative dashboard metrics calculated from the sample dataset.
14. Common Errors and Troubleshooting
| Problem | Possible Reason | Solution |
|---|---|---|
| FIXED result does not change after filtering | Ordinary dimension filters execute after FIXED. | Check whether the filter should be added to context. |
| Unexpected total values | Repeated FIXED values are being aggregated again. | Review the outer aggregation and calculation grain. |
| Incorrect customer totals | Wrong dimension or measure selected. | Verify the Customer and Sales field references. |
| Invalid calculation syntax | Missing braces, colon or aggregation. | Check the complete FIXED expression syntax. |
| Incorrect multiple-dimension result | Dimensions define a different level of granularity. | Verify all dimensions inside FIXED. |
| Unexpected date calculation | Date field is not being grouped at the intended level. | Use the correct date function and date granularity. |
15. Best Practices for FIXED LOD Expressions
- Clearly identify the required calculation level before creating the expression.
- Use only the dimensions needed for the calculation.
- Use meaningful calculated field names.
- Choose the correct aggregation function for the measure.
- Understand how context filters affect FIXED calculations.
- Check whether the result is repeated across multiple marks.
- Avoid unnecessary nested calculations.
- Test expressions using a small sample dataset.
- Compare FIXED results with independently calculated totals.
- Document complex business calculations for future maintenance.
Summary
FIXED LOD expressions in Tableau calculate measures at explicitly specified dimensions independently of the dimensions displayed in the visualization. They can be used to calculate customer-level, region-level, product-level and order-level values using aggregation functions such as SUM, AVG, MIN, MAX, COUNT and COUNTD. FIXED expressions also support multiple dimensions and date-based calculations. Understanding Tableau's order of operations is important because context filters are applied before FIXED expressions, while ordinary dimension filters are applied afterward. These capabilities make FIXED LOD expressions useful for advanced reporting, customer analysis and business performance dashboards. In the next lesson, you will learn how INCLUDE and EXCLUDE LOD expressions work with practical examples.