Understanding LOD Expressions in Tableau

What Are LOD Expressions in Tableau?

Level of Detail (LOD) expressions in Tableau allow users to define the level at which a calculation should be performed, regardless of the visualization’s aggregation level. This enables more precise control over calculations and improves flexibility in data analysis.

LOD expressions are an advanced concept and often cause confusion. The simplest way to think about them is that they allow you to perform calculations at a specific level of granularity, regardless of what is being displayed in the view.

Tableau provides three types of LOD expressions:

• FIXED – Calculates values at a specific dimension level, ignoring other filters.
• INCLUDE – Performs calculations at a finer level than the visualization but still respects filters.
• EXCLUDE – Omits certain dimensions from aggregations, helping with comparisons and subtotals.

How to Write LOD Expressions in Tableau

LOD expressions are written using the { } syntax. Here’s an example of a FIXED LOD calculation:

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

This calculates total sales per customer, ignoring any filters applied in the visualization.

Understanding FIXED LOD in Tableau

The FIXED LOD expression computes a value at a specified level of granularity, regardless of what’s in the view.

Example: Calculate Average Sales Per Region

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

This ensures that the average sales are always calculated per region, even if other fields are added to the view.

INCLUDE and EXCLUDE LOD Expressions

INCLUDE and EXCLUDE expressions offer different levels of aggregation:

Example: INCLUDE for Finer Granularity

{ INCLUDE [Product] : SUM([Sales]) }

This calculation ensures that sales are summed at the product level, even if the visualization only contains broader dimensions like region.

Example: EXCLUDE for Removing Granularity

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

This calculates total sales while ignoring the Category field, useful for comparing subcategories within broader groups.

SQL Equivalents to LOD Expressions

LOD expressions in Tableau essentially replicate SQL functionality that’s been abstracted away for ease of use. Here’s how each LOD type maps to SQL concepts:

FIXED LOD → GROUP BY

FIXED LOD expressions are most directly equivalent to SQL’s GROUP BY clause:

-- Tableau: { FIXED [Customer ID] : SUM([Sales]) }
SELECT 
    customer_id,
    SUM(sales) as total_customer_sales
FROM sales
GROUP BY customer_id

INCLUDE LOD → Subqueries or Window Functions

INCLUDE LOD expressions are similar to window functions or correlated subqueries in SQL:

-- Tableau: { INCLUDE [Product] : SUM([Sales]) }
SELECT 
    region,
    product,
    SUM(sales) OVER (PARTITION BY product) as product_total_sales
FROM sales

EXCLUDE LOD → Nested Queries or Window Functions

EXCLUDE LOD expressions can be replicated using nested queries or window functions that ignore certain dimensions:

-- Tableau: { EXCLUDE [Category] : SUM([Sales]) }
SELECT 
    category,
    subcategory,
    sales,
    SUM(sales) OVER (PARTITION BY subcategory) as sales_without_category
FROM sales

Key Differences

• SQL is more explicit: While LOD expressions hide complexity, SQL requires you to explicitly define your data transformations
• Performance: SQL calculations are performed at the database level, while LOD expressions are processed by Tableau’s engine
• Flexibility: SQL offers more granular control over calculations, while LOD expressions provide a simplified interface for common aggregation patterns

Easier Expressions with SQL in Evidence

In Evidence, all transformations are performed using SQL (DuckDB dialect) before rendering reports. Instead of using LOD expressions, users can write queries that define aggregations explicitly, which can be easier to understand and debug.

For more details about the Evidence SQL dialect, see the Evidence Queries page.