Graphed LogoGraphed
Login

What is Level of Detail in Tableau?

Cody Schneider•

One of the trickiest concepts to grasp when learning Tableau is "Level of Detail." But understanding Level of Detail (LOD) expressions is what elevates you from someone who can make basic charts to someone who can truly answer complex business questions with data. This quick guide will break down what Tableau Level of Detail expressions are, why they’re so useful, and how to use the three main types: FIXED, INCLUDE, and EXCLUDE.

What is ‘Level of Detail’ in Tableau, Anyway?

In data visualization, the "level of detail" refers to the granularity of your data as displayed in a worksheet. Think about a dataset of store sales. You could look at it at different levels:

  • High-level (less granular): Total sales for the entire company.

  • A bit more detailed: Sales broken down by country.

  • Even more detailed (more granular): Sales broken down by state within each country.

  • Highly granular: Sales for each individual customer transaction.

Normally, the level of detail in a Tableau sheet is determined by the dimensions you drag onto your 'Rows', 'Columns', or 'Marks' cards. If you drag the [Region] dimension onto 'Rows' and [SUM(Sales)] onto 'Columns,' your view's level of detail is the 'Region' level. Every calculation you make, like AVG([Profit]), will be performed for each region separately.

But what if you want to perform a calculation at a different level of detail than what's in your view? What if, while looking at sales by state, you also want to see how each state's sales compare to the total sales for its entire region? This is where LOD expressions come in. They allow you to compute values at a level of detail that is independent of your visualization’s structure.

Why You Need Level of Detail Expressions: A Practical Example

Let's stick with the idea of comparing state sales to the regional total. Imagine your manager asks for a simple bar chart showing sales for each state in the 'West' region, but she also wants a reference line showing the total sales for the entire West region for comparison.

Here’s the problem:

  • Your view is detailed at the state level (California, Washington, Oregon, etc.). So, SUM([Sales]) gives you the sum for each state.

  • To get the total sales for the West region, you'd usually have to remove the 'State' dimension. But doing that collapses your entire chart into one bar for the West region, which isn't what you want.

You’re stuck. You need to calculate one value at the state level and another value at the regional level, simultaneously, in the same view.

An LOD expression solves this perfectly. You could create a calculated field called 'Regional Sales' like this: {FIXED [Region] : SUM([Sales])}. Now, you can drag your regular SUM([Sales]) onto columns to get the state bars, and drag your new 'Regional Sales' field into the Detail card to create a reference line. The LOD calculates the total for the entire region and 'stamps' that value onto every row belonging to that region, regardless of the states shown in your view.

Suddenly, you have a visualization that answers a much more interesting question: "How much is each state contributing to the regional total?"

The Three Types of LOD Expressions

Tableau offers three different types of LOD expressions, distinguished by the keywords FIXED, INCLUDE, and EXCLUDE. Each serves a distinct purpose.

1. FIXED Level of Detail Expressions

FIXED is the most straightforward and powerful type of LOD. It calculates a value using only the dimensions you specify in the formula, completely ignoring other dimensions in the view.

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

  • When to use it: When you need a value calculated at a specific level that should not change, even as users filter or change the dimensions in the view.

Example: Finding the First Purchase Date for Each Customer

A classic application is cohort analysis. You want to group customers based on when they made their first purchase. Your raw data has a row for every transaction, so a simple MIN([Order Date]) will change depending on what's in your view.

Instead, create a calculated field called Customer First Order:

{ FIXED [Customer Name] : MIN([Order Date]) }

When you drag this field into your view alongside [Customer Name], every order from that customer will be associated with the same date: their very first purchase date. You can now use this to group customers into cohorts (e.g., "January 2023 Signups") independent of how you slice and dice their other transaction data.

One key thing to remember about FIXED: Regular filters in your view will not affect a FIXED LOD calculation unless you "Add to Context." Think of context filters as being applied before the FIXED calculation runs.

2. INCLUDE Level of Detail Expressions

The INCLUDE LOD comes into play when you need to perform a calculation at a more granular level than what is shown in your visualization, without actually adding that granular dimension to the view.

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

  • When to use it: When you want to add nuance or detail to a calculation. The final computation takes into account the dimensions in your view plus any dimensions specified in the formula.

Example: Finding the Average of Customer Order Sizes per Category

Imagine your view shows each Product Category and you have AVG([Sales]) on columns. Tableau dutifully calculates the average sales price of every single product sold within each category.

But what you actually want to know is, "For each category, what is the average total amount each customer spends?" This is a different question. It requires you to first find the total sales per customer within each category, and then find the average of those totals.

Create a calculated field called Avg Customer Sales by Category:

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

This expression goes down to the customer level (even though [Customer Name] isn't in your view), sums up their sales, and brings that value up. To finish, you don't drop this field directly onto the sheet. Instead, you change the aggregation of the "pill" on your columns shelf to average:

The calculation will then be AVG({ INCLUDE [Customer Name] : SUM([Sales]) }). Tableau will now properly answer your complex question.

3. EXCLUDE Level of Detail Expressions

As the name suggests, EXCLUDE does the opposite of INCLUDE. It subtracts a dimension from the view's level of detail, allowing you to calculate values at a less granular level than what's displayed.

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

  • When to use it: When you need to calculate a broader total for comparison purposes, temporarily ignoring some of the detail in your view.

Example: Percent of Total Category Sales for Each Sub-Category

Let's say your report shows Product Category and Product Sub-Category on the rows, showing sales for each. You want to know what percentage of the 'Technology' category's total sales comes from 'Phones' vs. 'Copiers,' etc.

To do this, you need two numbers for each row: the Sub-Category sales and the total Category sales. The first is easy: SUM([Sales]). For the second, you need a way to calculate total sales while ignoring the Sub-Category detail.

Create a calculated field called Category Total Sales:

{ EXCLUDE [Sub-Category] : SUM([Sales]) }

This expression computes the sum of sales for the Product Category in each row, effectively ignoring the Sub-Category dimension in its calculation. So, for both the 'Phones' and 'Copiers' rows, this field will show the same value: the total sales for 'Technology'.

Finally, you can create a third calculation for your percentage:

SUM([Sales]) / SUM([Category Total Sales])

Now you can easily display what percentage each sub-category contributes to its parent's total sales.

Final Thoughts

Tableau's Level of Detail expressions are the key to unlocking a deeper level of analysis that goes beyond what's immediately visible in your views. By mastering FIXED, INCLUDE, and EXCLUDE, you gain precise control over your calculations, enabling you to build more insightful, flexible, and powerful dashboards that answer the real questions behind the data.

Building complex reports in visual reporting builders like Tableau is powerful, but it often comes with a steep learning curve and hours spent wrestling with syntax or tweaking visualizations. As your data questions become more complex and span across different platforms like Google Analytics, Shopify, and your CRM, the setup and analysis process can become a major time sink. That's precisely why we built Graphed. We wanted to eliminate the manual drudgery behind powerful analytics so teams can get straight to the insights. Just connect your data sources in a few clicks, and ask questions in plain English - like "create a dashboard comparing Facebook Ads spend vs Shopify revenue by campaign for last month" and get a real-time, interactive dashboard in seconds.