Tableau LODs: Fixed, Include, Exclude with Use Cases

February 24, 2023

This post was originally published on The Data School blog between 2018 and July 2025, before our program was renamed to MIP’s Analytics Career Accelerator. References throughout this article to “The Data School” or “DS” all refer to what is now MIP’s Analytics Career Accelerator. The program, its people, and its commitment to launching outstanding analytics careers remain the same – just under a new name.

If you are having trouble viewing this article, please report it here.

LOD expressions, also known as Level of Detail expressions, enable computation of values at both the data source and visualization levels while providing control over the desired level of granularity for computation. This can be achieved by performing expressions at a more granular level (INCLUDE), less granular level (EXCLUDE), or an entirely separate level (FIXED).

Fixed LOD expressions: These expressions compute a value at a specified level of aggregation regardless of the visualization filters applied to the view. They are denoted by enclosing the dimension(s) in curly braces { } and are useful when you want to aggregate data at a higher level of detail than the view’s current level of detail.
Include LOD expressions: These expressions let you specify a dimension or dimensions to include in the computation of an aggregate value. They are denoted by the keyword INCLUDE and can be used to create a dynamic set of values based on one or more dimensions.
Exclude LOD expressions: These expressions let you specify a dimension or dimensions to exclude from the computation of an aggregate value. They are denoted by the keyword EXCLUDE and can be used to create a dynamic set of values based on one or more dimensions.

I will be using the dataset Orders (Sample – EU Superstore) for the purpose of the demonstration

FIXED LOD:

Example: Calculate the Maximum Sales per Order

In this scenario, we aim to determine the sales and maximum sales for each order, which may consist of one or more product IDs. To achieve this, we use FIXED LOD expressions to create a calculated field called “Max Sales per Order.” Additionally, we can make use of a helpful tip developed by Andy that translates the LOD expressions into plain English sentences, which is available in this link.

Example 2: When did the Customer Order Last?

Similar to the previous example, we create a calculated field “Last Order Date by Customer” to fix the customer ID and then calculate the last order date.

Example 3 : How do the Sales of Each Sub-Category Compare to the Overall Category Sales?

Firstly, we create a FIXED LOD expression named “Category Sales.” It is important to note that this expression generates a single value for each category (e.g., for the category “Furniture,” the value is 779,103).

Next, we create another calculated field named “% Total Subcategory to Overall Category Sales.” This field calculates the ratio of each sub-category’s sales to the overall sales of the corresponding category.

For SUM(Sales) in the Measure Values, right click “Edit Table Calculation”, then choose the Calculation Type: Percent of Total and computing using Pane(down), which gives us the same result as the “% Total Subcategory to Overall Category Sales” in the last step

In this example, if we add a dimension filter for “Region” and select “Central,” the table calculation portion will change accordingly. However, the FIXED LOD expression for “Category Sales” remains static because the FIXED LOD filter operates before the Dimension Filter.

If we right-click on the “Region” filter and select “Add to Context,” the first column in the table will become dynamic as we filter by region (e.g., Central) for sales. This is because context filters have a higher order of operation than dimension filters, and thus the category sales will adjust based on the filtered region.

INCLUDE LOD:

The INCLUDE LOD expressions include the level of detail of the specified dimension(s) in addition to other dimensions present in the view, thus providing more granularity to the data.

Example 1: What is the Average Customer Sales Amount per Region?

I applied Andy’s Mantra to translate the INCLUDE LOD of the ‘Sales Per Customer’ into FIXED LOD here. For each Region(the current dimension) including Customer ID, Calculate the Sum of Sales. Therefore, it is equivalent to:

{ FIXED [Customer ID],[Region]: SUM([Sales])}

By using the INCLUDE LOD, it allows me to calculate at a finer level of detail in the database and then re-aggregate and show at a coarser level of detail in the view.

EXCLUDE LOD:

EXCLUDE LOD is the Opposite of INCLUDE LOD, declares dimensions to omit from the view level of detail. I’ve found it useful when calculating ‘Percent of Total’

Example1: What is the Average Discount per Category within each Category?

Similarly, the EXCLUDE LOD expression used in “Avg Discount by Category” can be interpreted as “for each current view (i.e., Category + Sub-Category), exclude Sub-Category and compute the average discount.” Therefore, the FIXED LOD expression for this scenario can be expressed as:

{ FIXED [Category]:AVG([Discount])}

Use Case: Use Nested LOD to Fix the Axis

In this example, we want to know the sum of sales in each region across different months. If we select the region “North” or “South,” the sales range only goes up to 35k. However, when we choose “Central,” the range soars to 90k. To solve this problem, we can create a Nested LOD expression to fix the y-axis at the same level.

Step1: Create a Calculated Field “Monthly Regional Sales” , and for each Region and Month, compute the Sum of Sales

Step2: Add Another FIXED LOD “Max Regional Sales” to return the max value for the monthly regional sales

Step3: Add the Sum(Max Regional Sales) to Detail

All done!