In my last blog I introduced Level of Detail (LOD) expressions and what they can be used to do. I showed an example of the Include function and touched on how it can be replicated with the Fixed function. In this blog I’ll go through an Exclude function example and again replicate it with the Fixed function.
But first, the ATTR() function
The ATTR() function should be introduced as it will pop up in our Exclude example. ATTR() is an aggregate function that stands for attribute. This function is used to return the value of a dimension if the value is the same for each row in the results set. Tableau uses the following formula to calculate the Attribute:
IF MIN([Dimension]) = MAX([Dimension])
THEN MIN([Dimension])
ELSE “*”
END
If there’s heterogeneity amongst the rows of a result set, the ATTR() function will return an ‘*’ instead of a value. This can be seen when you are viewing data at a certain level of detail and drag a dimension with a higher granularity onto the tooltip. Because there are multiple values for that dimension at the displayed granularity, Tableau cannot list them and instead returns an asterix.
The Exclude function
Unsurprisingly, the Exclude function is the opposite of the Include function; It aims to lower granularity for a certain calculation by first taking the data to a higher aggregation. This function would be used, for example, if you wanted to display geographical data at a city level but aggregate it at a state or country level for a particular insight.
A quick example
Using the SuperStore data set in Tableau, we can show the SUM(Sales) for each City. For simplicity only cities in Alabama and Arizona are shown below:
But what if we wanted to know the percentage contribution of each city to the state total sales? We’d first need a LOD calculation returning the sum of Sales aggregated at state level for each city. This can be achieved with the following formula:
This formula tells Tableau that we want to EXCLUDE city in the SUM([Sales]) aggregation and go down one level of granularity to state. Adding this measure to the cross tab we get:
Notice that instead of SUM, Tableau has aggregated our new calculated field with ATTR(). This is because aggregating in another way at this level in would not make sense – all the values are the same at the city level for each state.
To get the city contribution to state total, we can divide [Sales] by [Exclude City – State Total]. After changing the format to a percentage we get the contribution of each city to total state sales:
Replicating this with the Fixed function, we simply swap ‘EXCLUDE’ for ‘FIXED’ and specify the level of detail at State:
Want to know more?
Bora Beran explains when and how to use the Include, Exclude or Fixed functions in this video.
Data Schooler Pris Lam has written a great blog about the Exclude function, as well as one each for the Include and Fixed functions.
Out of sight, out of Mind: LOD expressions (Part 1)
