What is LOD in Tableau?

If you've ever built a chart in Tableau, you know the calculation depends on the dimensions you've dragged into the view. Change the dimensions, and the calculation updates. But what if you need to calculate a value at a different level of detail? This is where Level of Detail (LOD) expressions come in, allowing you to create more powerful and flexible analyses. This guide will walk you through the three types of LODs - FIXED, INCLUDE, and EXCLUDE - with practical examples to show you how they work.

What Problem Do LOD Expressions Solve?

By default, visualizations in Tableau compute calculations based on the level of detail, or "granularity," of the dimensions present in the view. For example, if you have Region and SUM(Sales) in your view, Tableau calculates the total sales for each region. If you then add Category to your view, the calculation automatically becomes the sum of sales for each category within each region.

This is usually what you want. But problems arise when you need to perform a calculation at a different grain than what's being displayed. Let's say you want to see each category’s sales as a percentage of its total regional sales. To do that, you need two numbers for your calculation:

1. The sales for each category in the region (which is at the view's level of detail).
2. The total sales for the entire region (which is at a higher level of detail, ignoring the category).

Without LOD expressions, obtaining that second number would require complex table calculations or data blending. LOD expressions solve this by letting you specify exactly which dimensions an aggregate calculation should consider, regardless of the dimensions in the view.

The Three Types of Tableau LOD Expressions

Level of Detail expressions provide a way to compute values at the data source level or the visualization level. Think of them as giving you the power to "zoom" your calculations in or out on your data. They all follow a similar syntax, starting and ending with curly braces { }. There are three types to know: FIXED, INCLUDE, and EXCLUDE.

1. FIXED LOD Expressions

A FIXED LOD expression computes a value using only the dimensions you explicitly specify in the formula. It "fixes" the calculation to a specific level, ignoring all other dimensions in the view unless they've been added to context filters. This is the most independent type of LOD.

Syntax: { FIXED [Dimension 1], [Dimension 2] : AGGREGATE([Measure]) }

Example: Finding a Customer's First Purchase Date

A common business need is to find when a customer first made a purchase. You might want to use this for cohort analysis or to see how long it takes for a new customer to buy again. Even if your view shows monthly sales or product categories, for this calculation, you only care about one dimension: the customer.

Step-by-step instructions:

1. Right-click in the Data pane and select "Create Calculated Field."
2. Name your calculation something like "Customer First Order Date."
3. Enter the following FIXED expression:
4. Click "OK."

This formula tells Tableau: "For each unique customer name, find the minimum (earliest) order date and return that date." Tableau computes this behind the scenes for every single customer in your dataset.

Now, you can use this field in countless ways. If you drag Customer Name, Customer First Order Date, and Order Date to your Rows shelf, you can see the very first purchase date repeated for every order that customer made. This allows you to create more advanced calculations, like days between first purchase and subsequent purchases, or group customers into cohorts based on the month or year they made their first purchase.

2. INCLUDE LOD Expressions

An INCLUDE LOD expression computes a value at a finer, more granular level than what is in the view. It considers all the dimensions currently in your visualization plus any dimensions you specify in the expression. It's perfect for when you need to "include" an extra layer of detail in a calculation that isn't visually present on the chart.

Syntax: { INCLUDE [Dimension 1], [Dimension 2] : AGGREGATE([Measure]) }

Example: Average Sales per Order by State

Imagine your boss asks for a bar chart showing the average sales per order for each state. Your first instinct might be to put State on Columns and AVG(Sales) on Rows. But this gives you the average sale amount of each individual item (each row) in your data, not the average total sales for each distinct order.

To get the correct number, you need to first calculate the sum of sales for each individual order ID, and then find the average of those totals for each state. Order ID isn't in our view, but we need to "include" it in our calculation.

Step-by-step instructions:

1. Create a new Calculated Field and name it "Sales per Order."
2. Enter the following INCLUDE expression:
3. Click "OK."

This expression tells Tableau: “For the dimensions in the view (like State), also include Order ID. Then, for each unique combination of dimensions in the view and Order ID, calculate the total sum of sales.” This effectively calculates the total value of each order.

Now, to build your final view:

1. Drag State to the Columns shelf.
2. Drag the new "Sales per Order" calculated field to the Rows shelf.
3. Right-click the "Sales per Order" pill in the Rows shelf and change its aggregation from SUM to Average.

You now have a bar chart that accurately displays the average total sales amount per order for each state. You were able to dig a level deeper than your visualization to get the number you needed.

3. EXCLUDE LOD Expressions

An EXCLUDE LOD expression is the opposite of INCLUDE. It calculates a value at a coarser, less granular level than what is in the view. It computes the calculation based on all dimensions in the view minus any dimensions you specify in the expression. This is useful for "percent of total" or "difference from overall average" scenarios.

Syntax: { EXCLUDE [Dimension 1], [Dimension 2] : AGGREGATE([Measure]) }

Example: Comparing Sub-Category Sales to Overall Regional Sales

Let's say you have a table showing sales broken down by Region and Sub-Category. You can easily see the sales for "Chairs" in the "East" region, but you also want a column that shows the total sales for the East region in its entirety, on every single row. This enables you to easily compare a specific sub-category to the regional total.

To get the total sales for the region, you need a calculation that sums sales while ignoring (excluding) the Sub-Category dimension that is actively breaking down your view.

Step-by-step instructions:

1. Create a new Calculated Field and name it "Sales by Region."
2. Enter the following EXCLUDE expression:
3. Click "OK."

This expression tells Tableau: “For every row, calculate the sum of sales based on the dimensions in the view, but ignore Sub-Category.” Since Region is the only other primary dimension in our imagined view, this calculation will return the total sales for each region.

You can now build a table with Region and Sub-Category on Rows and both SUM(Sales) and SUM(Sales by Region) on Text. You'll see the individual sub-category sales right next to the total regional sales, repeated on each line. From here, you could create another calculated field like SUM(Sales) / SUM(Sales by Region) to find each sub-category's percentage of its regional total.

Quick Tips for Using LODs

• Understand Tableau's Order of Operations: Where filters are applied matters. FIXED expressions are computed before regular dimension filters, meaning a dimension filter won't affect a FIXED calculation. If you want a filter to apply to your FIXED LOD, you must right-click the filter in the Filters shelf and select "Add to Context." INCLUDE and EXCLUDE expressions are applied after dimension filters.
• Check Your Aggregation: When you drag an LOD calculation into the view, Tableau wraps it in an aggregation (like SUM or AVG). For FIXED calculations, this is often a good thing to avoid visual errors, but sometimes you might want to view it as a Dimension or use ATTR() (Attribute) if you expect the same value for every row.
• Test on a Simple Table First: Before building your final, beautiful chart, build a simple text table with the dimensions involved. Drag your LOD calculation in and see if the numbers match what you intuitively expect. This makes it much easier to debug and verify your logic.

Final Thoughts

This may seem complex at first, but mastering Tableau's Level of Detail expressions unlocks a new level of analytical capability. By using FIXED, INCLUDE, and EXCLUDE, you break free from the constraints of your visualization's layout, allowing you to ask deeper, more specific questions of your data and create much richer dashboards that provide more context.

We know that learning the nuances of tools like Tableau and writing complex calculations can feel like a steep learning curve that keeps you from getting quick answers. That's why we built Graphed. We connect directly to your data sources, whether it's Google Analytics, Shopify, or your CRM, and let you create entire dashboards simply by describing what you want in plain English. Instead of figuring out the perfect LOD syntax, you can just ask, "What were the average sales per customer, by region last quarter?" and get a live, interactive dashboard in seconds.