How to Use FIXED in Tableau

Calculating values outside the current viz level of detail is one of the most powerful things you can do in Tableau, and the FIXED Level of Detail (LOD) expression is an essential tool for the job. Mastering this function is a major step toward moving from a beginner to an advanced Tableau user. This article will break down how FIXED works, show you practical examples, and explain the crucial role of context filters.

What Are Level of Detail (LOD) Expressions in Tableau?

Imagine you have a chart showing your company's sales broken down by state. If you put SUM(Sales) on the view, Tableau automatically shows you the sum of sales for each individual state. The "level of detail" of the view is the State level. But what if you want to also show the total sales for the entire Region each state belongs to, right next to the state's sales? How do you tell Tableau to calculate one metric at the State level and another at the Region level, all in the same chart?

This is where Level of Detail expressions come in. An LOD expression lets you perform calculations at a different level of granularity than what is currently shown in your visualization. It’s like telling Tableau: "For this specific calculation, ignore the dimensions I have in this view and calculate this value using a different set of rules."

Tableau has three main types of LOD expressions:

• FIXED: Calculates a value using only the specific dimensions you list. It ignores all other dimensions in the view. This is what we'll be focusing on.
• INCLUDE: Calculates a value using the dimensions in the view plus any other dimensions you specify.
• EXCLUDE: Calculates a value using the dimensions in the view but omitting any dimensions you specify.

These functions let you answer more complex business questions, such as comparing an individual’s performance to a team average or analyzing how customers from different sign-up cohorts behave over time.

Diving into the FIXED LOD Expression

The FIXED LOD expression forces the aggregation to a very specific level. The calculation remains "fixed" on the dimensions you tell it to use, regardless of what else is happening in your visualization (with one important exception we'll cover later).

Breaking Down the FIXED Syntax

The syntax for a FIXED LOD is straightforward. It always follows this structure:

{ FIXED [Dimension 1], [Dimension 2] : AGGREGATE([Measure]) }

Let's unpack each piece:

• {} (Curly Braces): The curly braces are what tell Tableau you're writing a Level of Detail expression. You must have an opening { and a closing }.
• FIXED: This keyword specifies the type of LOD expression you are creating.
• [Dimension 1], [Dimension 2], ... : This is the most important part. Here you list the dimensions at which you want Tableau to perform the calculation. You could fix it at the [Region] level, at the [Customer ID] and [Product Category] level, or any other combination. These are the dimensions that will define the aggregation's granularity.
• : (Colon): The colon separates the dimensions from the aggregation you want to perform.
• AGGREGATE([Measure]): This is the calculation itself. It could be SUM([Sales]), AVG([Profit]), MIN([Order Date]), or COUNTD([Order ID]).

For example, if you wanted to calculate the total sales for each region, the formula would be:

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

This tells Tableau: "For each Region, calculate the SUM of Sales and give me that single value. Even if I add State or City into my view later, always give me the total value for the entire Region."

Practical Examples: Putting FIXED to Work

The best way to understand FIXED is to see it in action. Let's walk through a few common scenarios using a standard sales dataset.

Example 1: Calculating Percentage of Regional Sales by State

The Goal: You want to create a table that shows each state's sales and also calculates what percentage that state contributes to its region's total sales.

To do this, you first need a way to get the total regional sales on every row for each state.

1. Create the Calculation for Regional Sales: Go to Analysis > Create Calculated Field. Name it Regional Sales and enter the following formula:

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

1. Build the View:

• Drag Region and State to the Rows shelf.
• Drag the original Sales measure to the Text mark on the Marks card (or double-click it to add it to the view).
• Now, drag your new Regional Sales calculated field to the view right next to SUM(Sales).

You'll see a table where the SUM(Sales) column shows a different value for each state, but the Regional Sales column shows the same value for every state within a given region. This is FIXED working its magic - it calculated SUM(Sales) at the [Region] level and is ignoring the [State] in the view.

Take It Further: Now it's easy to find the percentage of a region's total. Create one more calculated field named % of Regional Sales with this formula:

SUM([Sales]) / SUM([Regional Sales])

Drag this new field to your view and format it as a percentage. Now you can easily see that California makes up a huge percentage of the West region's sales, while Wyoming accounts for a much smaller slice.

Example 2: Finding a Customer's First Purchase Date

The Goal: You want to know when each customer made their very first purchase. This is foundational for any cohort analysis, helping you understand if customers who started in January behave differently than those who started in June.

1. Create the First Purchase Date Calculation: Create a new calculated field called First Purchase Date and enter:

{ FIXED [Customer ID] : MIN([Order Date]) }

This formula tells Tableau to look through all orders for a single [Customer ID], find the minimum (earliest) [Order Date], and return that specific date.

1. Build the View:

• Drag Customer Name or Customer ID to the Rows shelf.
• Drag your new First Purchase Date field next to it. You might need to right-click it and change the format to "Exact Date". You now have a searchable list of every customer and the date of their first order.

This date is now a static attribute for that customer. You can use it to create cohorts, like grouping all customers whose First Purchase Date was in 2022 and comparing their lifetime value against the 2023 cohort.

Example 3: Comparing to a Grand Total

The Goal: You need to create a simple bar chart showing what percentage of total company-wide sales each Product Category contributes.

You can use FIXED without any dimensions to get a grand total for the entire dataset.

1. Create the Grand Total Calculation: Make a calculated field named Total Company Sales with this formula:

{ FIXED : SUM([Sales]) }

Because there are no dimensions listed after FIXED, this calculation is performed over your entire dataset. It gives you one number: the total sum of sales.

1. Create the Percent of Total Calculation: Now, make another calculated field called % of Total Sales.

SUM([Sales]) / SUM([Total Company Sales])

1. Build the View:

• Drag Category to the Rows shelf.
• Drag your new % of Total Sales field to the Columns shelf.
• Format the field as a percentage and sort descending.

You now have a clean bar chart showing each category's contribution to the total. And here's where it gets interesting - if you now drag Region onto the Filters shelf, this chart won't change. That’s because FIXED calculations are processed before standard filters. Let's explore why.

The Key to FIXED: Tableau's Order of Operations

This is the concept that trips up most users. Tableau processes filters and performs calculations in a very specific order. A simplified version of this "pipeline" looks like this:

1. Extract & Data Source Filters (these happen first)
2. Context Filters
3. FIXED LOD Calculations (computed here)
4. Dimension Filters
5. INCLUDE / EXCLUDE LOD Calculations
6. Measure Filters
7. Table Calculation Filters

The crucial insight is that FIXED LODs are computed before regular Dimension Filters. A dimension filter is any blue pill you drag to the Filters shelf, like Region or Category.

This means if you have a FIXED calculation and filter by a standard dimension, the calculation is evaluated first on the entire dataset, and then the filter is applied to the viz, which only hides data from view. The underlying FIXED number doesn't change.

How to Make a Filter Affect a FIXED Calculation

So, what if you want your country filter to affect your {FIXED [Category] : SUM([Sales])} calculation? Simple: you need to Add it to Context.

A context filter becomes a higher-priority filter. You're telling Tableau to create a temporary smaller dataset based on that filter's selection before it calculates the FIXED LOD.

To add a filter to context, simply right-click the filter on the Filters shelf and select "Add to Context." The pill will turn gray, indicating that it is now a context filter.

Try it with the % of Total Sales chart from Example 3. As soon as you add the Region filter to context, your bar chart will update to show each category's contribution to sales for just the selected region. The "total" in % of Total Sales is no longer the total for the entire company, but the total for only the data left after the context filter is applied.

Final Thoughts

The FIXED level of detail expression is a foundational concept in Tableau that gives you precise control over your calculations. By allowing you to move beyond the granularity of your view, you can perform powerful comparisons and create ratios that provide much deeper context to your reports. The key is to understand both its syntax and its place in Tableau's order of operations, especially in relation to context filters.

When you're trying to solve reporting challenges manually, there can be a steep learning curve. The time spent digging through forums to get a calculation right is time not spent acting on insights. I wanted to eliminate that friction, which is why we built Graphed. As your AI data analyst, it lets you connect all your data sources and create sophisticated dashboards just by describing what you need. Instead of wrestling with syntax, you can just ask, "Show me a dashboard of Shopify revenue vs. Facebook Ads spend by campaign," and let the platform handle the technical details for you - in seconds.