This blog is part of the How To Use Level of Detail blog series. This blog will cover the FIXED Level of Detail expression and function.
Simply put, FIXED expressions aggregate the value ONLY at the dimensions specified in the calculation. Unlike the INCLUDE/EXCLUDE expression, the FIXED expression doesn’t take into consideration the dimensions in the view. However, here’s the plot twist. FIXED expressions are like the shapeshifters of LOD in that they can have a finer, coarser, or even the same level of detail than the view. Therefore, how you use it is dependent on the dimensions existing in the view already.
A common use case for FIXED is to find a specific value for a category E.g. a cohort. Let’s say we wanted to build a view that showed us each city’s performance and also highlighted which state had the top performing city. We would start by building a calculation to find the total sales in each state. For FIXED LOD calculations, we would read it from left to right with a “For each… Find the…” sentence. This calculation would return the total sum of sales for each state as requested.
Building on top of this example, we could extend the calculation to bind the max State by Sales with a nested calculation ( a calculation within a calculation). The second part of this calculation would allow us to find the top-performing state in the city. If the city’s sum of sales is the same as the top performing state in the country, then label it ‘Max’. I could then use this calculation to spotlight the state in my chart and allow two levels of details to concurrently occur in my view.
What’s the difference between INCLUDE and FIXED then?
As I mentioned in the EXCLUDE blog, there are cases where an INCLUDE/EXCLUDE and FIXED calculation may return a similar result. E.g. An Include City: Max Sales can have the same result as a FIXED City: Max Sales if the viz has only State as a dimension in view. The image below is an example of similar looking values for INCLUDE and FIXED.
It’s key, therefore, to remember that the difference is that:
- FIXED calculations are not relative to the dimensions in the view
- FIXED calculations are higher in the Tableau Order of Operations. A FIXED expression will trump an INCLUDE/EXCLUDE LOD Calc, meaning that if they were both
When we add City into the Row shelf, the view is now a finer grain than the FIXED calculation we made. You can see that in the view there are 2 dimensions (State and City) where else our FIXED Calculation has only 1 dimension. Now because our FIXED calculation is coarser than the view, our values duplicated down the pane. In such situations, it becomes more obvious the use for FIXED vs INCLUDE or EXCLUDE.