How to Use LOD in Tableau

Calculating values at the right level of detail is fundamental to data analysis, but it isn't always straightforward. Tableau's Level of Detail (LOD) expressions give you the power to compute aggregations at a level of granularity that's different from what’s currently in your view. This article will walk you through the three types of LOD expressions - FIXED, INCLUDE, and EXCLUDE - with practical examples to show you how and when to use them.

What Are LOD Expressions Anyway?

In Tableau, the "level of detail" of your visualization is simply the combination of dimensions you've dragged onto your shelves (like Rows, Columns, Color, or Detail). For instance, if you drag the Region dimension to Rows and SUM(Sales) to Columns, the level of detail is "Region," and Tableau calculates the total sales for each region.

But what if you want to see how each region's sales compare to the "average regional sales" right in the same table? Or calculate the total sales for a customer and use that value across different views, regardless of what other dimensions are present?

This is where LOD expressions come in. They let you create calculations that are not tied to the dimensions in your view. You can explicitly tell Tableau which dimensions to consider (or ignore) for a specific calculation, giving you much more control and flexibility for deeper analysis.

There are three types of LOD expressions you can use:

• FIXED: Calculates an aggregate value for the specific dimensions you list, regardless of what other dimensions are in the view.
• INCLUDE: Calculates an aggregate value using the dimensions in the view plus any additional dimensions you specify.
• EXCLUDE: Calculates an aggregate value using the dimensions in the view but subtracting any dimensions you specify.

The Basic Syntax of an LOD Expression

All LOD expressions follow a similar structure, which is always enclosed in curly braces {}. Understanding this syntax is the first step to mastering them.

{[LOD KEYWORD] [Dimension(s)] : [Aggregate Expression]}

Let's break that down:

• {}: The entire expression is always wrapped in a set of curly braces.
• LOD KEYWORD: This is where you put FIXED, INCLUDE, or EXCLUDE.
• [Dimension(s)]: After the keyword, you declare the dimension or multiple dimensions (separated by commas) you want to use for the calculation. For example, [Customer Name] or [Region], [Category].
• :: A colon separates your dimension declaration from the aggregate part of the expression.
• [Aggregate Expression]: This is the calculation you want to perform, such as SUM([Sales]) or COUNTD([Order ID]).

Now, let's see how each of the keywords works with a hands-on example using Tableau's sample Superstore dataset.

FIXED LOD: Calculating an Absolute Value

A FIXED LOD expression computes a value using only the dimensions you specify. It completely ignores all other dimensions in the view. This makes it perfect for calculating values that need to remain constant as you filter or drill down into your data, like cohort-specific metrics or customer lifetime value.

When to use FIXED:

• When you need a value that is independent of your visualization's structure.
• To compare a segment to an overall total (e.g., this product’s sales vs. all product sales).
• For cohort analysis (e.g., finding each customer's first purchase date and tying all future actions back to it).

Example: Finding Sales per Customer

Let's say we want to find the total sales for each customer and then see the average of those totals for each region. A simple AVG(Sales) would give us the average sales per transaction, but we want the average total sales per customer.

1. Create a new calculated field. Let's call it "Sales per Customer (FIXED)".
2. Enter the following formula:
3. Now, let's use it. Drag Region to the Rows shelf.
4. Drag your new "Sales per Customer (FIXED)" field to the Text shelf on the Marks card. By default, Tableau might aggregate it as a SUM. Change the aggregation by right-clicking the pill, selecting Measure, and choosing Average.

Your view will now show the average of each customer's total sales, broken down by region. If you were to add another dimension, like Category to your Rows, the value would not change. Why? Because the FIXED calculation computes the total for each customer before the view's dimensions are even considered.

A Quick Tip: FIXED LODs are computed before regular dimension filters in Tableau's order of operations. This means if you filter your view by Category = 'Technology', your FIXED calculation will not be affected. To make a filter apply to your FIXED calculation, right-click the filter in the Filters shelf and select Add to Context.

INCLUDE LOD: Adding More Detail

An INCLUDE LOD expression computes an aggregation using all the dimensions currently in the view plus any additional dimensions you specify in the expression. Think of it as adding a temporary layer of detail to your calculation without changing your whole visualization.

When to use INCLUDE:

• When you want your calculation to be more granular than your visualization.
• To find an average of sums, like "What is the average sales amount per customer in each state?" when your view only shows states.

Example: Average Sales of "Super Customers"

Imagine your view shows total sales for each Sub-Category. Now, you want to identify which sub-categories have a higher average of large orders. Specifically, you want to find the average order value for orders over $500.

An INCLUDE expression is perfect for this. We can calculate the sales for each individual order ID, even though Order ID isn't in our view.

1. Create a calculated field called "Order Total".
2. Enter:
3. Create a second calculated field named "Avg Value of Large Orders".
4. Enter:
5. Now build your view. Drag Sub-Category to the Rows shelf.
6. Drag the "Avg Value of Large Orders" calculation to the Columns shelf. Tableau might default to SUM(), change it to AVG() by right-clicking the pill.

You now have a bar chart showing the average value of large orders for each sub-category, letting you quickly see which categories drive high-value purchases. We calculated this at the Order ID level of detail without ever needing to add Order ID to our view.

EXCLUDE LOD: Removing Detail

Opposite to INCLUDE, an EXCLUDE LOD expression subtracts dimensions from your view's level of detail. It calculates the aggregation at a higher level than what you see on the screen. This is extremely useful for 'percent of total' calculations or for comparing an individual value to an aggregated parent value.

When to use EXCLUDE:

• To compare an individual's value against a sub-total (e.g., a state's sales vs. its region's sales).
• To calculate a value that is relative to a parent category without needing complex table calculations.

Example: State's Contribution to Regional Sales

Let's say our view shows Sales by Region and State. We want an extra column that shows the total sales for the region right next to each state, so we can calculate what percentage of the region's sales each state is responsible for.

1. Start by dragging Region and then State to the Rows shelf.
2. Drag Sales into the Text shelf. You will now see sales for each state.
3. Create a calculated field called "Regional Sales (EXCLUDE)". Enter:
4. Drag "Regional Sales (EXCLUDE)" to the view right next to your SUM(Sales) pill. You’ll see the full regional total repeated for every state within that region.
5. For the final touch, create one more calculated field called "Percent of Region Total":

Quick Reference: Which LOD to Choose?

Sometimes the hardest part is knowing which LOD expression is the right tool for the job. Here's a simple way to think about it:

• Use FIXED when: You need a value that is completely independent of the dimensions in your view. Ask: "I need to calculate [measure] for each [dimension(s)], no matter what."
• Use INCLUDE when: You want to calculate at a finer level of detail than your view. Ask: "I need my SUM(Sales) calculation to also consider [dimension] even though it's not on my chart."
• Use EXCLUDE when: You want to calculate at a broader level of detail than your view. Ask: "I need my SUM(Sales) calculation to ignore [dimension] so I can see the total."

Final Thoughts

Mastering Tableau's Level of Detail expressions is one of the biggest steps you can take toward advanced analytics. They unlock the ability to conduct more nuanced comparisons, build robust dashboards, and answer complex business questions directly within your visualizations.

Of course, learning advanced functionality in tools like Tableau takes a serious time investment. For teams that need answers fast, we built Graphed to simplify the entire process. Instead of writing formulas to handle levels of detail, you can just ask a question like "show me each state's contribution to regional sales as a percentage" in plain English, and Graphed builds the interactive visualization for you automatically. We handle the technical busywork of data reporting so you can get insights in seconds, not hours.