Was this page helpful?
Yes No
Have a comment? Please leave it here. Thanks for your feedback!
All Tableau Help > Tableau Help > 
Design Views and Analyze Data > Advanced Analysis > Level of Detail Expressions > Overview: Level of Detail Expressions
Applies to: Tableau Desktop, Tableau Online, Tableau Public, Tableau Server

Overview: Level of Detail Expressions

Level of detail expressions (which are sometimes also referred to as "LOD Expressions" or "LOD Calculations") are useful for a variety of use cases, including:

In this article:

Row Level Expressions and View Level Expressions

In Tableau, expressions referencing unaggregated datasource columns are computed for each row in the underlying table. In this case, the dimensionality of the expression is row level. An example of a row-level expression is:

[Sales] / [Profit]

This calculation will be evaluated in each row of the database. For each row, the Sales value in that row will be divided by the Profit value in that row, producing a new column with the result of the multiplication (a profit ratio).

If you create a calculation with this definition, save it with the name [ProfitRatio], and then drag it from the Data pane to a shelf, Tableau typically aggregates the calculated field for the view:

SUM[ProfitRatio]

By contrast, expressions referencing aggregated data source columns are computed at the dimensionality defined by the dimensions in the view. In this case, the dimensionality of the expression is view level. An example of a view-level expression is:

SUM(Sales) / SUM(Profit)

If you drag this calculation to a shelf (or type it directly on a shelf as an ad-hoc calculation), Tableau encloses it in an AGG function:

AGG(SUM(Sales) / SUM(Profit))

This is what is known as an aggregate calculation. For details, see Aggregate Calculations.

Dimension and set fields placed on any of the locations highlighted in the following image contribute to the view level of detail:

Before level of detail expressions were supported in Tableau, it was not possible to create calculations at a level of detail other than the view level. For example, if you attempt to save the following expression, Tableau displays the error message: “Cannot mix aggregate and non-aggregate arguments with this function”:

[Sales] – AVG([Sales])

The user’s intent in this case was to compare store sales for each individual store to the average of sales for all stores. This can now be accomplished with a level of detail expression:

[Sales] - {AVG([Sales])}

This is what is known as a table-scoped level of detail expression. See Table-Scoped Level of Detail Expressions

Level of Detail Expression Syntax

A level of detail expression has the following structure:

{[FIXED | INCLUDE | EXCLUDE] <dimension declaration > : <aggregate expression>}

The elements in a level of detail expression are described in the following table.

Element Description
{ } The entire level of detail expression is enclosed in curly braces.
[FIXED | INCLUDE | EXCLUDE]

The first element after the opening curly brace is one of the following scoping keywords:

  • FIXED

    FIXED level of detail expressions compute values using the specified dimensions without reference to the view level of detail—that is, without reference to any other dimensions in the view.

    FIXED level of detail expressions also ignore all the filters in the view other than context filters, data source filters, and extract filters.

    Example: { FIXED [Region] : SUM([Sales]) }

    See FIXED Level of Detail Expressions.

  • INCLUDE

    INCLUDE level of detail expressions compute values using the specified dimensions in addition to whatever dimensions are in the view.

    INCLUDE level of detail expressions are most useful when including a dimension that isn’t in the view.

    Example: { INCLUDE [Customer Name] : SUM([Sales]) }

    See INCLUDE Level of Detail Expressions.

  • EXCLUDE

    EXCLUDE level of detail expressions explicitly remove dimensions from the expression—that is, they subtract dimensions from the view level of detail.

    EXCLUDE level of detail expressions are most useful for eliminating a dimension in the view.

    Example: {EXCLUDE [Region]: SUM([Sales])}

    See EXCLUDE Level of Detail Expressions.

In the case of a table-scoped level of detail expression, no scoping keyword is required. See Table-Scoped Level of Detail Expressions

The examples in the respective sections on the different types of level of detail expressions are simple examples. For more creative and advanced examples, aligned with real-world use cases, see the Tableau blog. Search for "level of detail expressions" or "LOD."

<dimension declaration>

Specifies one or more dimensions to which the aggregate expression is to be joined. Use commas to separate dimensions. For example:

[Segment], [Category], [Region]

For level of detail expressions, you can use any expression that evaluates as dimension in a dimensionality declaration, including Date expressions.

This example will aggregate the sum of Sales at the Year level:

{FIXED YEAR([Order Date]) : SUM(Sales)}

This example will aggregate the sum of Sales for the [Order Date] dimension, truncated to the day date part. Because it is an INCLUDE expression, it will also use the dimensions in the view to aggregate the value:

{INCLUDE DATETRUNC('day', [Order Date]) : AVG(Profit)}

Note: It is strongly recommended that you drag fields into the calculation editor when creating dimension declarations, instead of typing them. For example, if you see YEAR([Order Date]) on a shelf and then type that as the dimension declaration, it will not match the field on the shelf. But if you drag the field from the shelf into the expression, it will become DATEPART('year', [Order Date]), and that will match the field on the shelf.

With named calculations (that is, calculations that you save to the Data pane, as opposed to ad-hoc calculations, which you do not name), Tableau cannot match the name of a calculation to its definition. So if you create a named calculation, MyCalculation, defined as follows:

MyCalculation = YEAR([Order Date])

And then you created the following EXCLUDE level of detail expression and used it in the view:

{EXCLUDE YEAR([Order Date]) : SUM(Sales)}

Then MyCalculation would not be excluded.

Similarly, if the EXCLUDE expression specified MyCalculation:

{EXCLUDE MyCalculation : SUM(Sales)}

Then YEAR([Order Date]) would not be excluded.

: A colon separates the dimension declaration from the aggregate expression.
<aggregate expression> The aggregate expression is the calculation performed to define the target dimensionality.

Limitations for Level of Detail Expressions

The following limitations and constraints apply for level of detail expressions. Also see Data Source Constraints for Level of Detail Expressions.

In addition, some data sources have complexity limits. Tableau will not disable calculations for these databases, but query errors are a possibility if calculations become too complex.

Level of Detail Expressions Can Be Dimensions or Measures

When you save a level of detail expression, Tableau adds it to either the Dimensions or the Measures area in the Data pane.

FIXED level of detail expressions can result in measures or dimensions, depending on the underlying field in the aggregate expression. So MIN([Date])} will be a dimension because [Date] is a dimension, and {fixed Store : SUM([Sales])} will be a measure because [Sales] is a measure. When a FIXED level of detail expression is saved as a measure you have the option of moving it to dimensions.

INCLUDE and EXCLUDE level of detail expressions are always measures.