This blog is part of the How To Use Level of Detail blog series. Today’s blog will cover the EXCLUDE Level of Detail expression and function.
The EXCLUDE function allows you to calculate a coarser granularity than the dimensions in your view. To calculate your desired expression, Tableau will first remove the specified excluded dimensions from the viz LOD and then perform the calculation as though the dimension is not present. All dimensions in the view will be aggregated except the excluded dimension.
A use case for this would be calculating how each year’s sales for in segment compares to the overall sales in each segment. As the chart has the ‘Order Date’ dimension in the row shelf, causing a finer level of detail, I would need to create a exclude LOD calculation to tell Tableau to remove the ‘Order Date’ dimension from the calculation.
The expression used to calculate this can be read as “Exclude Order Dates from the Sum of Sales”.
When looking at our EXCLUDE calculation vs Sales measure in a cross tab, you can see that the Exclude level of detail expression causes replicated values to appear in the view. Also, our EXCLUDE LOD expression has defaulted to the ATTR aggregation. Tableau does this to indicate that the expression is not actually being aggregated and that changing the aggregation of the calculation will have no effect on the view. You will also notice that in this scenario, an EXCLUDE expression would have the same result as a FIXED LOD expression. However, INCLUDE and EXCLUDE LODs are considered after Dimension filters, so depending on the order you want to use filters, an EXCLUDE LOD expression may be more suitable.
Putting the EXCLUDE calculation we created into use, I can now build a bar chart in with the Year and Segment granularity but place in the tool tip a calculation that uses the higher Segment dimension only.
Read more from the How To Use Level of Detail in Tableau series:
How To Use Level of Details – General Level of Detail