How to Use Tableau Fixed Function

While working on the Tableau reports, I noticed that the calculations I had applied were changing according to the dimensions I had added to the view. For example, I added a calculation to display total sales by customer. When adding another field, such as Region or Category, the results changed, making it difficult to display fixed sales totals.

As a solution, I used the FIXED Level of Detail (LOD) expression. Using the FIXED function in Tableau, we can fix the calculations at a specific level of detail, ignoring the fields in the view and filter or dimensions we have applied.

In this tutorial, I will explain the Tableau FIXED function and how we can use it to get consistent results from the calculations.

Table of Contents

FIXED Function in Tableau

In Tableau, the FIXED Level of Detail (LOD) function allows us to calculate the value of a measure for a specific dimension. It works based on the dimensions we set, regardless of whether other fields are added or removed from the view. This means we can lock the calculation to specific dimensions, ensuring consistent results that remain unchanged with the worksheet’s layout or filters.

For example, we want to find the total sales made by each customer. For this, we will use the FIXED function, as in { FIXED [Customer Name]: SUM([Sales])}. Now, Tableau will always calculate sales at the customer level, even if we add other fields, such as Region or Category, to the view.

Syntax to use the FIXED Function in Tableau:

{ FIXED [Dimension1], [Dimension2] : AGG([Measure])}

Here, Dimension1 and Dimension2 are the fields on which we want to fix your calculation. Then, the Measure is the value we want to calculate, which can be SUM, AVG, COUNT, etc.

Use the FIXED Function in Tableau

In the example below, we will see how we can create a calculation using the FIXED function in Tableau. After creating the calculated field using FIXED, we will apply it to a view and compare the results with a regular aggregation.

Example-1: Display Sales per Customer Using Fixed Function

In this example, we will create a calculated field using the FIXED function to show total sales per customer. Generally, when we add the dimensions Region and Customer Name with the measure Sales, then we will get sales for each combination of customer and region.

Using the FIXED() function, we can lock the calculation of Sales for Customer Name. With this, even if we add other dimensions, it will show the calculation of Sales only for the Customer Name.

Now, connect the Tableau with the Superstore data set and follow the steps below.

Select a sheet and to create a calculated field, click on the Analysis tab and select Create Calculated Field.

Label the calculated field as Customer Sales, then enter the formula below that will fix the Sales calculation for the Customer Name.

{FIXED[Customer Name]:SUM([Sales])}

Now, to create the visualisation, add the Customer Name to the Rows and the calculated field Customer Sales to the Columns.

With this, we can see the total Sales for each customer.

Now, add another dimension, such as Category, ahead of Customer Name in the Rows. Here, we can see the calculation of customer total sales even after adding another dimension.

If we have not used the FIXED function in the calculated field, then after adding another dimension, the calculation for the combination of both dimensions, Customer Name and Category, will change, as shown in the image below.

After adding another dimension, the Tableau will give the calculation combining both dimensions. In this case, it will show sales of customers in the Category dimension instead of the customer’s total sales.

Example -2: Calculate Average Profit Per Region Using Fixed Function

In this example, we will use the FIXED() function to calculate the average profit for each Sub-Category. This fixed calculation will always return the average profit for each Sub-category, even if you include fields like Region or Order Date.

Now, open the Tableau worksheet and follow the steps below to create a calculated field using the FIXED() function.

To create a calculated field, click on the Analysis tab and select Create Calculated Field.

Label the calculated field as Average Profit and enter the formula below.

{FIXED [Sub-Category] : AVG([Profit])}

To visualise the data, add the Sub-Category to the Rows and then add the calculated field to the Text card in the Marks section. With this, we can see the average profit for each sub-category.

Now, even if we add another dimension to the view, the calculation for the profit average will be the same for the Sub-category. To show this, I will add the Region dimension to the Rows.

Here, we can see the profit average is displayed for the Sub-Category instead of the combined profit average of Region and Sub-Category.

If we had created this view without the fixed function, then it would have calculated the profit average combined for both dimensions as shown in the image below.

Implement FIXED function in Tableau Calculation

This way, we can use the FIXED Level of Detail (LOD) function in Tableau to calculate and fix the value of a measure for a specific dimension.

Conclusion

In this Tableau tutorial, we learned how to use the FIXED function in Tableau for consistent calculations. By using the FIXED function, instead of changing with every new field we add to the view, we can lock the calculation for a selected dimension.

In the above examples, we calculated total sales per customer and average profit per sub-category, and both remained the same even when we added extra dimensions, such as Region or Category.

Tableau Fixed Function

FIXED Function in Tableau

Use the FIXED Function in Tableau

Example-1: Display Sales per Customer Using Fixed Function

Example -2: Calculate Average Profit Per Region Using Fixed Function

Conclusion

DOWNLOAD FREE AGENTFORCE EBOOK

FREE SALESFORCE FLOW EBOOK