EXCLUDE Level of Detail Expressions
For foundational concepts about level of detail (LOD) expressions, see Create Level of Detail Expressions in Tableau and How Level of Detail Expressions Work in Tableau.
Level of detail expressions are used to modify or control the level of detail (granularity) of a calculation. Most fields and calculations are tied to the granularity of the view or of the data source. LOD expressions allow you to set the granularity of a calculation explicitly.
- FIXED LOD expressions establish a specific dimension or dimensions for the rest of the calculation, such as returning the maximum blood pressure for each patient instead of the overall maximum in the data set by fixing the maximum to Patient ID.
- INCLUDE LOD expressions ensure that a dimension that isn't necessarily present in the view is considered in the calculation, such as looking at the average blood pressure for each patient including their sex, displayed in a view that doesn't contain sex information.
- EXCLUDE LOD expressions remove some of the detail from the view for the purposes of the calculation, such as looking at the average blood pressure for patients on a certain medication without considering individual patients, even if the view is by patient.
Note: Whenever a FIXED LOD is used, it will give the same result regardless of the visualization. When an INCLUDE or EXCLUDE LOD is used, the value might change depending on the structure of the view. The INCLUDE keyword takes the context of the view and adds a dimension for the calculation to consider (and the EXCLUDE removes a dimension present in the view from the context of the calculation), so using the calculation in another context can offer different results.
Consider a view (A) built with patient ID, age, and sex, and another view (B) with patient ID, age, and weight.
- A FIXED LOD expression will have the same value in both cases because the calculation ignores what is in the view and cares only about the dimension in the LOD expression.
- An INCLUDE LOD that includes weight will have a different value in each view, because it adds a dimension to view A but not to view B, which already had weight.
- An EXCLUDE LOD that excludes weight will have a different value in each view, because it removes a dimension from view B but doesn't change the context of view A, which already didn't have weight.
EXCLUDE
EXCLUDE level of detail expressions prevent the calculation from using one or more of the dimensions present in the view.
EXCLUDE level of detail expressions are useful for ‘percent of total’ or ‘difference from overall average’ scenarios. They are comparable to such features as Totals and Reference Lines.
EXCLUDE level of detail expression cannot be used in row-level expressions (where there are no dimensions to omit), but can be used to modify either a view level calculation or anything in between (that is, you can use an EXCLUDE calculation to remove dimension from some other level of detail expression).
Example 1
The image below shows the change in average blood pressure over time for four countries, broken out by sex.
To see the average blood pressure for each country over time but without being split by male and female, use an EXCLUDE level of detail expression {EXCLUDE [Sex] : AVG[Average blood pressure]}. This is plotted as the grey line in the viz.
Bringing an average line out from the Analytics pane wouldn't work, because it would simply be a horizontal line for the average blood pressure per country, ignoring sex and year.
Note: This example takes the average of averages which is an incorrect analytical practice. It is done here purely for illustration purposes. No analysis should be made from this visualization.
Example 2
The following level of detail expression excludes [Region] from a calculation of the sum of [Sales]:
{EXCLUDE [Region]: SUM([Sales])}
The expression is saved as [ExcludeRegion].
To illustrate how this expression might be useful, first consider the following view, which breaks out the sum of sales by region and by month:
Dropping [ExcludeRegion] on Color shades the view to show total sales by month but without the regional component:
Example 3
The following EXCLUDE level of detail expression computes the average sales total per month and then excludes the month component:
{EXCLUDE [Order Date (Month / Year)] : AVG({FIXED [Order Date (Month / Year)] : SUM([Sales])})}
Notice that this is a nested level of detail expression—that is, a level of detail expression within another level of detail expression.
Saved as [average of sales by month], the calculation can then be subtracted from the sum of sales per month by means of an ad-hoc calculation on the Rows shelf:
With Month([Order Date]) on the Columns shelf, this creates a view that shows the difference between actual sales per month over a four-year period and the average monthly sales for the entire four-year period:
