LOD Expressions in Tableau

Tableau LOD (Level of Detail) expressions are used to find answers to questions that involve multiple levels of granularity in a single visualization. With the help of LOD expressions, you can compute aggregations that are not at the detail level of visualization. You can also incorporate the aggregation values within the data visualizations in many ways and give more control to determine the granularity level in your computations.

How to create LOD expressions?

Step 1: Set up the Visualization

1. Open the Tableau Desktop and add data source Sample-Superstore.
2. Navigate to a new worksheet.
3. From the Data pane, under Dimensions, drag the field Region to the Columns Shelf.
4. From the Data pane, under Measures, drag the field Sales to the Rows Shelf.

Step 2: Create the LOD expression

Instead of calculating the sum of all sales per region, maybe you also want to see the average sales per customer in each region.

1. Go to Select Analysis > Create Calculated Field.
2. In the Calculation editor, do the following:

• Name the calculation as Sales Per Customer.
• Enter the below LOD expression:

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

3. When finished, click OK.

This newly created LOD expression will add to the Data pane, under the Measures shelf.

Step 3: Use LOD expression in the visualization

1. From the Data pane, under Measures shelf, drag the field Sales Per Customer from the Rows shelf and drop it on the left of field SUM (Sales).
2. On the Rows shelf, right-click on the Sales Per Customer and select Measure (Sum) > Average.

Now you can see that it will display both the sum of all sales and the average sales per customer for each region.

Limitations of Tableau LOD:

• The LOD expressions that refer to the floating-point measures depict unpredictable behavior when used in a view that demands a comparison of the expression values.
• For LOD expressions, when you reference a dimensionality declaration parameter, you must always use the parameter name and not the parameter value.
• When you perform data blending, you must ensure that the primary data source’s linking field is in the view before you can use another level of detail expression from the secondary data source.

Row Level & View Level Expressions

Row Level: Expressions that reference unaggregated data source columns are computed for each row in the table below. Here, the dimensionality of the expression is row level. Given below is an example of a row-level expression:

[Sales] / [Profit]

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

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

SUM[ProfitRatio]

View Level: On the other hand, expressions that reference aggregated data source columns are computed at dimensionality defined by the dimensions of the view. Here, the dimensionality of the expression is view level. Given below is an example of a view-level expression:

SUM(Sales) / SUM(Profit)

If you drag the above calculation to a shelf, Tableau encloses it in an AGG function:

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

This is known as an Aggregate Calculation. 

Adding a LOD Expression to the View:

Whether a LOD in Tableau expression is aggregated or replicated in the view, it is determined by the expression type and granularity.

• INCLUDE expressions will have either the same LOD as the view or a finer detail. Therefore, values will never be replicated.
• FIXED expressions can have a finer detail level than the view, a coarser level of detail, or the same detail level. The necessity to aggregate the results of a FIXED LOD depends on what dimensions are in the view.
• EXCLUDE 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 being aggregated and that changing the aggregation will have no effect on the view.

Types of LOD expressions:

There are four types of LOD expressions in Tableau:

• FIXED
• INCLUDE
• EXCLUDE
• Table-Scoped

FIXED: FIXED LOD expressions compute a value using the specified dimensions without reference to the view’s dimensions. The following FIXED LOD expression computes the sum of sales per region:

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

This detail expression level, named [Sales by Region], is then placed on Text to show the total sales per region. The view LOD is [Region] plus [State], but because the FIXED level of detail expressions does not consider the view LOD, the calculation uses only the dimension referenced in the calculation, in this case, is Region. Due to this, you can also see that the values for the individual states in each region are identical.

If the INCLUDE keyword has been used in the LOD expression instead of FIXED, the values will be different for each state because Tableau would then add the dimension of the expression ([Region]) with any of the additional dimensions in the view ([State]) while determining values for the expression. The result would be as follows:

INCLUDE: INCLUDE LOD expressions compute values using the specified dimensions in addition to whatever the dimensions are in the view. INCLUDE LOD expressions can be useful when you want to calculate it at a fine level of detail in the database, and then re-aggregate and then show at a coarser level of detail in your view. Fields based on INCLUDE LOD expressions will change as you add or remove dimensions from the view.

Example 1

The below INCLUDE LOD expression computes total sales per customer:

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

When the above calculation is placed on the Rows shelf, aggregated as AVG, and the dimension [Region] is placed on the Columns shelf, the view shows the average customer sales amount per region:

If the measure [Sales] is then dragged to the Rows shelf, the result shows the difference between the total sale of each region and the average sale per customer of each region:

Example 2

The below INCLUDE level of detail expression calculates the sum of sales on a per-state basis:

{ INCLUDE [State] : SUM(Sales)}

The above calculation is placed on the Rows shelf and is aggregated as an average. The resulting visualization will average the sum of sales by state across categories.

When the Segment is added to the Columns shelf, and the calculation is moved to Label, the LOD expression results are updated. Then you can see how the average sum of sales per state varies across the categories and segments.

EXCLUDE: EXCLUDE LOD expressions to declare dimensions to omit from the view level of detail. EXCLUDE LOD expressions are useful for percent of total or difference from overall average scenarios. They are comparable to features such as Totals and Reference Lines.

EXCLUDE LOD expression are not used in row-level expressions (where there are no dimensions to omit) but it can be used to modify either a view level calculation or anything in between (i.e., you can use an EXCLUDE calculation to remove the dimension from some other level of detail expression).

Example 1

The following EXCLUDE LOD expression computes the average sales total per month and will then exclude the component month:

{EXCLUDE [Order Date (Month / Year)] : AVG({FIXED [Order Date (Month / Year)] : SUM([Sales])})}

Having Month([Order Date]) on the Columns shelf will create a view that shows the difference between actual sales per month over four years and the average monthly sales for the entire four-year period:

Example 2

The following LOD expression excludes [Region] from calculating the sum of [Sales]:

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

The expression is saved as [ExcludeRegion].

To show how this expression might be useful, consider the following example, which breaks the sum of sales by region and by month:

Dropping [ExcludeRegion] on Color will shade the view to show total sales by month but without the regional component:

Table-Scoped: It is possible to define a LOD expression at the table level without using any scoping keywords. For example, the below expression returns the minimum (earliest) order date for the entire table:

{MIN([Order Date])}

This is equivalent to a FIXED LOD expression with no dimension declaration:

{FIXED : MIN([Order Date])}

LOD expression syntax:

A LOD expression has the following structure:

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

The elements in a LOD expression are described in the following table.

{ }	The entire LOD expression is enclosed in curly braces.
[FIXED | INCLUDE | EXCLUDE]	The first element just after the opening curly brace is one of the following scoping keywords: FIXED:FIXED LOD expressions computes values using the specified dimensions without referencing to the view level of detail, i.e., without reference to another dimensions in the view.FIXED LOD expressions also ignore all the filters in the view other than context filters, data source filters, and extract filters.Example: { FIXED [Region] : SUM([Sales]) } INCLUDE:INCLUDE LOD expressions computes values using the specified dimensions in addition to whatever dimensions are in the view.INCLUDE LOD expressions is most useful when including a dimension that isn’t in the view.Example: { INCLUDE [Customer Name] : SUM([Sales]) } EXCLUDE:EXCLUDE LOD expressions explicitly remove dimensions from the expression, i.e., subtracting dimensions from the view level of detail.EXCLUDE LOD expressions are most useful for eliminating a dimension in the view.Example: {EXCLUDE [Region]: SUM([Sales])} Table-Scoped:In the case of a table-scoped LOD expression, no scoping keyword is required.
<dimension declaration>	It 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 the LOD expressions, you can use any expression that evaluates as a dimension in a dimensionality declaration, including the Date expressions.This example aggregates the sum of Sales at the Year level:{FIXED YEAR([Order Date]) : SUM(Sales)} This example aggregates the sum of Sales for the [Order Date] dimension, truncated to the day-date part. Since 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)} With named calculations (i.e., calculations that you save to the Data pane), Tableau cannot match the name of a calculation to its definition. Therefore, if you create a named calculation, MyCalculation, defined as follows:MyCalculation = YEAR([Order Date]) And then you created the following EXCLUDE LOD 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.