Level of Detail Expressions and Aggregation
The level of detail of the view determines the number of marks in your view. When you add a level of detail expression to the view, Tableau must reconcile two levels of detail – the one in the view, and the one in your expression.
The behaviour of a level of detail expression in the view varies depending on whether the expression's level of detail is coarser, finer or the same as the level of detail in the view. What do we mean by “coarser” or “finer” in this case?
Level of Detail Expression is Coarser Than View Level of Detail
An expression has a coarser level of detail than the view when it references a subset of the dimensions in the view. For example, for a view that contained the dimensions [Category] and [Segment], you could create a level of detail expression that uses only one of these dimensions:
{FIXED [Segment] : SUM([Sales])}
In this case, the expression has a coarser level of detail than the view. It bases its values on one dimension ([Segment]), whereas the view bases its view on two dimensions ([Segment] and [Category]).
The result is that using the level of detail expression in the view causes certain values to be replicated – that is, to appear multiple times.
Replicated values are useful for comparing specific values against average values within a category. For example the following calculation subtracts average sales for a customer from the average sales overall:
[Sales] - {FIXED [Customer Name] : AVG([Sales])}
When values are being replicated, changing the aggregation for the relevant field in the view (for example, from AVG to SUM) will not change the result of the aggregation.
Level of Detail Expression is Finer Than View Level of Detail
An expression has a finer level of detail than the view when it references a superset of the dimensions in the view. When you use such an expression in the view, Tableau will aggregate results up to the view level. For example, the following level of detail expression references two dimensions:
{FIXED [Segment], [Category] : SUM([Sales])}
When this expression is used in a view that has only [Segment] as its level of detail, the values must be aggregated. Here’s what you would see if you dragged that expression to a shelf:
AVG([{FIXED [Segment]], [Category]] : SUM([Sales]])}])
An aggregation – in this case, average – is automatically assigned by Tableau. You can change the aggregation as needed.
Adding a Level of Detail Expression to the View
Whether a level of detail expression is aggregated or replicated in the view is determined by the expression type (FIXED, INCLUDE or EXCLUDE) and whether the expression’s granularity is coarser or finer than the view’s.
INCLUDE level of detail expressions will have either the same level of detail as the view or a finer level of detail than the view. Therefore, values will never be replicated.
FIXED level of detail expressions can have a finer level of detail than the view, a coarser level of detail or the same level of detail. The need to aggregate the results of a FIXED level of detail depends on what dimensions are in the view.
EXCLUDE level of detail expressions always cause replicated values to appear in the view. When calculations including EXCLUDE level of detail expressions are placed on a shelf, Tableau defaults to the ATTR aggregation (as opposed to SUM or AVG) to indicate that the expression is not actually being aggregated and that changing the aggregation will have no effect on the view.
Level of detail expressions are always automatically wrapped in an aggregate when they are added to a shelf in the view, unless they are used as dimensions. So, if you double-click on a shelf and type
{FIXED[Segment], [Category] : SUM([Sales])}
and then press Enter to commit the expression, what you now see on the shelf is
SUM({FIXED[Segment], [Category] : SUM([Sales])})
But if you double-click into the shelf to edit the expression, what you see in edit mode is the original expression.
If you wrap a level of detail expression in an aggregation when you create it, Tableau will use the aggregation you specified rather than assigning one when any calculation including that expression is placed on a shelf. When no aggregation is needed (because the level of detail of the expression is coarser than that of the view), the aggregation you specified is still shown when the expression is on a shelf, but it is ignored.