FIXED LOD — Use FIXED LOD for per-entity metrics (e.g., per customer) - Tableau

SECTION 1

Historical Context & Motivation

Business intelligence tools have long struggled with a fundamental tension: how do you display data at one granularity while computing metrics at another? Early SQL-based reporting tools forced analysts to write complex subqueries or correlated aggregations whenever they needed, say, a per-customer total displayed alongside a per-order breakdown. This tension became especially pronounced as self-service analytics platforms rose in popularity during the 2000s. Business users wanted drag-and-drop simplicity, but the underlying data models often demanded multi-level aggregation logic that only SQL experts could craft efficiently.

2003 • Tableau Founded at Stanford

Chris Stolte, Pat Hanrahan, and Christian Chabot commercialize VizQL, a visual query language that translates drag-and-drop actions into database queries. At this stage, all aggregations are tied to the visualization's level of detail.

2009 • Table Calculations Introduced

Tableau introduces table calculations—post-aggregate computations like running totals and percent-of-total—that run on the result set rather than the database. These address some multi-granularity needs but are limited by the marks already in the view.

2015 • Level of Detail (LOD) Expressions Ship in Tableau 9.0

Tableau releases FIXED, INCLUDE, and EXCLUDE LOD expressions, enabling analysts to declare the exact grain at which an aggregation should occur. This is a paradigm shift: for the first time, the computation grain is decoupled from the viz grain without writing raw SQL.

2020+ • LOD Becomes Industry Standard Practice

LOD expressions become a staple of Tableau certification exams and enterprise dashboard design. FIXED LOD, in particular, emerges as the most widely used variant for entity-level KPIs like per-customer revenue, per-product margin, and per-employee performance.

The core question that LOD expressions answer is deceptively simple: How can I compute an aggregate at a specific entity level—say, per customer—and then use that value in a visualization that operates at a completely different grain, such as per region or per month? Before LOD expressions, the most common workarounds involved pre-aggregated data sources, complex data blending, or nested SQL. The FIXED LOD expression eliminates these workarounds by letting the analyst specify the computation grain declaratively, inline, within a calculated field.

SECTION 2

Core Principles & Definitions

To understand FIXED LOD, you need to internalize how Tableau's query pipeline works. When you place dimensions and measures on a shelf, Tableau constructs a query whose level of detail (LOD) is determined by the set of dimensions in the view. A FIXED LOD expression overrides this default behavior by specifying its own set of dimensions, independent of what is on the viz shelves. This decoupling is the foundational insight that makes per-entity metrics possible.

Declarative Grain Specification

A FIXED LOD expression explicitly names the dimension(s) that define the computation's granularity. The syntax { FIXED [Customer ID] : SUM([Sales]) } tells Tableau to compute total sales per customer, no matter what dimensions appear in the visualization.

Independence from Viz Filters

FIXED LOD calculations are evaluated before dimension filters in Tableau's order of operations. This means dimension-level filters do not alter the FIXED computation unless you explicitly add the field to context. This is analogous to a SQL subquery that executes independently of the outer WHERE clause.

Reusable Scalar per Entity

The result of a FIXED LOD expression is a scalar value attached to each unique combination of the specified dimensions. If you FIXED on [Customer ID], each customer gets exactly one value. That value is then replicated or further aggregated as needed by the viz grain.

Contrast with INCLUDE and EXCLUDE

INCLUDE adds dimensions to the viz LOD; EXCLUDE removes them. FIXED ignores the viz LOD entirely and substitutes its own. Think of INCLUDE and EXCLUDE as relative adjustments, while FIXED is an absolute specification—much like absolute vs. relative paths in a file system.

✦

KEY TAKEAWAY

Think of a FIXED LOD expression as a precomputed lookup table. Just as you might create a hash map keyed by customer ID and valued by total spend in a program, { FIXED [Customer ID] : SUM([Sales]) } constructs a dictionary where each key is a customer and each value is their aggregate. Tableau then joins this dictionary back to every row, so any visualization—regardless of its own granularity—can reference the per-customer metric.

SECTION 3

Visual Explanation — How FIXED LOD Decouples Grain

The diagram illustrates how a FIXED LOD expression computes at the customer grain (producing 3 scalar values) while the visualization displays at the region grain (2 rows). The lower pipeline shows that FIXED LOD is evaluated before dimension filters, which is critical for understanding filter interaction behavior.

Notice how the raw data contains six rows across three customers, yet the FIXED expression collapses this to exactly three values—one per customer. When the visualization groups by Region, Tableau further aggregates those three customer-level totals using whatever outer aggregation you specify (AVG, SUM, MIN, etc.). This two-stage aggregation is the defining characteristic of FIXED LOD: the inner aggregation is locked to the entity you declare, while the outer aggregation adapts to whatever the visualization requires. If you think of this in relational algebra terms, the FIXED expression is essentially a GROUP BY on the specified dimension followed by a natural join back to the original relation, after which the viz applies its own GROUP BY.

SECTION 4

Syntax, Semantics & Execution Model

FIXED LOD Syntax

GENERAL SYNTAX

{ FIXED [dim₁], [dim₂], … : AGG([measure]) }

Where [dim₁], [dim₂], … are the dimensions defining the computation grain, AGG is any Tableau aggregate function (SUM, AVG, MIN, MAX, COUNTD, etc.), and [measure] is the field being aggregated. The curly braces are mandatory and signal an LOD expression to the Tableau parser.

PER-CUSTOMER TOTAL SALES

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

Returns a single scalar per customer. Equivalent SQL: SELECT customer_id, SUM(sales) FROM orders GROUP BY customer_id — the result is then joined back to every row sharing that customer_id.

PER-CUSTOMER ORDER COUNT

{ FIXED [Customer ID] : COUNTD([Order ID]) }

Counts the distinct orders per customer. Useful for computing average order frequency or segmenting customers by purchase behavior.

Outer Aggregation Requirement

A subtle but critical point: when the FIXED dimension is at a finer grain than the viz, Tableau must further aggregate the FIXED result to match the viz level. For instance, if your viz shows regions and your FIXED field returns per-customer totals, Tableau wraps the FIXED expression in an outer aggregation—AVG({ FIXED [Customer ID] : SUM([Sales]) }) would give the average customer spend per region. Conversely, when the FIXED dimension is at a coarser grain than the viz, the single FIXED value is replicated across all finer rows—analogous to a broadcast join in distributed computing.

💡

Empty FIXED

A FIXED expression with no dimensions—{ FIXED : SUM([Sales]) }—computes the grand total across the entire data source. This is equivalent to a SQL SELECT SUM(sales) FROM orders with no GROUP BY. It produces a single scalar replicated to every row, making it useful for percent-of-total calculations: SUM([Sales]) / { FIXED : SUM([Sales]) }.

SECTION 5

LOD Expression Types — FIXED vs. INCLUDE vs. EXCLUDE

Tableau provides three LOD expression types, each with a distinct relationship to the dimensions present in the visualization. Understanding these differences is essential for choosing the right tool. The following diagram and table provide a comprehensive comparison, using a data set where the viz-level dimensions are [Region] and [Category].

Side-by-side comparison of the three LOD expression types. FIXED specifies an absolute grain, INCLUDE adds dimensions to the viz grain, and EXCLUDE removes dimensions from the viz grain. For per-entity metrics, FIXED is almost always the correct choice.

Property	FIXED	INCLUDE	EXCLUDE
Grain relationship	Absolute — independent of viz	Relative — viz grain + extra dims	Relative — viz grain − specified dims
Affected by dim filters?	No (unless added to context)	Yes	Yes
SQL analogy	Subquery with independent GROUP BY	Adding column to main GROUP BY	Removing column from main GROUP BY
Typical use case	Per-customer total, cohort assignment, grand totals	AVG of per-customer order sizes	Region-level total ignoring sub-category

Feature comparison of the three LOD expression types

SECTION 6

Worked Example — Average Customer Spend by Region

Suppose you are working with Tableau's sample Superstore data set and you need to build a bar chart showing the average total spend per customer in each region. Without FIXED LOD, Tableau would compute AVG([Sales]) at the row level, giving you the average transaction amount—not the average customer spend. The FIXED expression solves this by first computing each customer's total, then averaging those totals per region.

Average Customer Spend by Region

1
Step 1 — Define the Per-Customer Metric

Create a calculated field named Customer Total Sales with the formula: { FIXED [Customer ID] : SUM([Sales]) }. This computes the sum of sales for each unique Customer ID across the entire data source. In SQL terms, this is a correlated scalar subquery: SELECT SUM(sales) FROM orders o2 WHERE o2.customer_id = o1.customer_id joined back to each row.

Each row now carries its customer's total spend as a new field.
2
Step 2 — Place Region on Columns

Drag [Region] to the Columns shelf. This sets the visualization grain to region level. Tableau will group data into four regions: Central, East, South, and West.
3
Step 3 — Apply the Outer Aggregation

Drag Customer Total Sales to the Rows shelf. Tableau will default to SUM as the outer aggregation, which sums all customer totals within each region—that gives you regional revenue, not average customer spend. Change the aggregation to AVG by right-clicking the pill and selecting Measure → Average. The resulting expression is effectively: AVG({ FIXED [Customer ID] : SUM([Sales]) }).

Each bar now represents the average total spend per customer in that region.
4
Step 4 — Verify with a Sanity Check

To confirm correctness, create a simple text table with [Customer ID] on Rows and SUM([Sales]) on Text, filtered to one region. Manually compute the average of those values. It should match the bar height for that region in your chart. This two-step verification process—compute per-entity, then re-aggregate—is a best practice when debugging LOD expressions.
5
Step 5 — Handle Filter Interactions

Add [Category] as a dimension filter to show only 'Technology'. Notice that the FIXED values do not change—each customer's total still includes all categories because FIXED is evaluated before dimension filters. If you want the FIXED calculation to respect this filter, right-click the filter pill and select 'Add to Context'. This promotes it in the order of operations so it executes before the FIXED LOD.

With context filter: per-customer Technology-only spend. Without: per-customer total across all categories.

SECTION 7

Strengths, Limitations & Common Pitfalls

Strengths	Limitations
Decouples computation grain from viz grain, enabling per-entity KPIs in any view.	Ignores dimension filters by default, which can produce unexpected results for users unfamiliar with the order of operations.
Produces deterministic, stable values — the same customer always gets the same total regardless of filters.	Cannot reference parameters inside the dimension list (only in the aggregate expression). You cannot write { FIXED [Parameter] : SUM([Sales]) }.
Can be nested inside other calculations, used in IF/THEN logic, and combined with table calculations for sophisticated analytics.	May generate a more complex underlying query, potentially impacting performance on large data sets if the FIXED dimension has very high cardinality.
Supports multiple dimensions in a single expression, enabling multi-key entity definitions (e.g., per customer per year).	Results are not cached across worksheets — each sheet re-evaluates the expression, though Tableau's query optimizer often consolidates identical subqueries.

Strengths and limitations of FIXED LOD expressions

⚠️

PITFALL ALERT

The most common mistake with FIXED LOD is forgetting that dimension filters do not affect FIXED calculations unless you add the filter to context. Think of it like variable scoping in programming: a FIXED expression lives in an outer scope that is not visible to the dimension filter's inner scope. To force the filter into the outer scope, you promote it to a context filter, which is analogous to moving a variable declaration to a higher scope level.

SECTION 8

Connection to Advanced LOD Patterns

Once you are comfortable with single-dimension FIXED expressions, a world of advanced patterns opens up. These patterns are ubiquitous in production Tableau dashboards at enterprise scale, and understanding them deepens your ability to model complex business logic declaratively.

Pattern	Expression	Use Case
Cohort Assignment	`{ FIXED [Customer ID] : MIN([Order Date]) }`	Assigns each customer to their first-purchase cohort for retention analysis.
Nested FIXED	`{ FIXED [Region] : AVG({ FIXED [Customer ID] : SUM([Sales]) }) }`	Computes per-customer totals, then averages them per region in a single calculated field.
Boolean Bucketing	`IF { FIXED [Customer ID] : SUM([Sales]) } > 500 THEN 'High' ELSE 'Low' END`	Segments customers into tiers based on total spend — usable as a dimension for color or filtering.
Percent of Entity Total	`SUM([Sales]) / { FIXED [Customer ID] : SUM([Sales]) }`	Shows what fraction of a customer's total spend each individual order or category represents.
Grand Total Reference	`SUM([Sales]) / { FIXED : SUM([Sales]) }`	Computes each row or group as a percentage of the overall total. The empty FIXED returns a single scalar.

Advanced FIXED LOD patterns for production dashboards

These patterns can be combined with table calculations for even more powerful analytics. For example, you might use a FIXED expression to compute each customer's first purchase date (cohort assignment), then use a table calculation like RUNNING_SUM to track cumulative retention over time. The key architectural insight is that FIXED LOD expressions are evaluated in Tableau's query phase (sent to the database), while table calculations execute in a post-query phase (computed in Tableau's memory). Understanding this pipeline is analogous to understanding the difference between compile-time and runtime evaluation in a compiled language.

SECTION 9

Practice Problems

Question 1: CONCEPTUAL

Explain why AVG([Sales]) computed at the viz level (with [Region] on Rows) gives a different result than AVG({ FIXED [Customer ID] : SUM([Sales]) }) at the same viz level. What fundamental difference in granularity accounts for the discrepancy?

Show answer

AVG([Sales]) at the region level computes the average of individual row-level sales values within each region. If a region has 1,000 transactions, it averages all 1,000 values. In contrast, AVG({ FIXED [Customer ID] : SUM([Sales]) }) first computes each customer's total spend (producing one value per customer), then averages those customer-level totals within each region. If the region has 200 customers, it averages 200 values. The FIXED version answers 'What is the average customer spend?' while the plain AVG answers 'What is the average transaction amount?'—two fundamentally different questions operating at different grains.

Question 2: BASIC CALCULATION

Given a data set with columns [Employee ID], [Department], and [Salary], write a FIXED LOD expression that computes each department's total payroll. Then write the outer aggregation you would use to display the maximum departmental payroll across the entire company as a single number on a dashboard.

Show answer

The FIXED expression is: { FIXED [Department] : SUM([Salary]) }. This computes total payroll per department. To display the maximum across all departments, wrap it in MAX: MAX({ FIXED [Department] : SUM([Salary]) }). When placed on a sheet with no dimensions on the shelves, this returns a single scalar—the payroll of the highest-spending department. The inner FIXED aggregates at the department grain, and the outer MAX aggregates those department-level values to a grand total grain.

Question 3: INTERMEDIATE

You have a Superstore-style data set. A manager asks for a bar chart showing, for each product category, the number of customers whose total lifetime spend exceeds $1,000. Write the calculated fields needed and describe how you would structure the view.

Show answer

First, create a calculated field for per-customer total spend: { FIXED [Customer ID] : SUM([Sales]) }. However, this gives total spend across all categories. If the manager wants category-specific spend, you need: { FIXED [Customer ID], [Category] : SUM([Sales]) }. Next, create a boolean field: IF { FIXED [Customer ID], [Category] : SUM([Sales]) } > 1000 THEN 1 ELSE 0 END. Place [Category] on Columns and COUNTD([Customer ID]) on Rows, but filter to only rows where the boolean field equals 1. Alternatively, use the boolean as a filter. This two-stage approach—FIXED for per-entity computation, then filtering on the result—is a standard pattern for threshold-based segmentation.

Question 4: APPLIED

A SaaS company wants a dashboard showing monthly churn. A customer is considered 'churned' if their most recent order was more than 90 days before the analysis date. Using FIXED LOD, write the expression to identify each customer's last order date, then describe how you would compute the monthly churn rate (number of churned customers ÷ total customers) and visualize it over time.

Show answer

Start with { FIXED [Customer ID] : MAX([Order Date]) } to get each customer's most recent order. Create a boolean: IF DATEDIFF('day', { FIXED [Customer ID] : MAX([Order Date]) }, [Analysis Date]) > 90 THEN 'Churned' ELSE 'Active' END. For a time-series view, you need to evaluate this condition relative to each month-end, not a single static date. One approach: create a parameter for the reference date and iterate it, or use a cross-join with a date spine. The churn rate per month is COUNTD(IF [Churn Status] = 'Churned' THEN [Customer ID] END) / COUNTD([Customer ID]). Place MONTH([Analysis Date]) on Columns and this ratio on Rows. The FIXED expression ensures each customer's last order date is stable regardless of the monthly grain of the visualization.

Question 5: CRITICAL THINKING

A colleague argues that FIXED LOD expressions are unnecessary because you can always achieve the same result with a pre-aggregated data source (e.g., a SQL view that already contains per-customer totals). Critically evaluate this claim. Under what conditions is the colleague correct, and when does FIXED LOD provide advantages that a pre-aggregated source cannot?

Show answer

The colleague is partially correct: any FIXED LOD computation can theoretically be replicated with a pre-aggregated SQL view or a materialized table. In fact, for very large data sets with millions of entities, a pre-aggregated approach may even outperform FIXED LOD because it avoids Tableau generating complex subqueries at query time. However, FIXED LOD provides several advantages that pre-aggregation cannot easily match. First, it enables rapid iteration: an analyst can define and modify per-entity metrics on the fly without waiting for ETL pipeline changes or DBA intervention—critical in exploratory analysis. Second, FIXED LOD expressions compose with other Tableau features (parameters, sets, calculated fields) dynamically, whereas a pre-aggregated view is static. Third, a single Tableau data source can support dozens of different FIXED LOD expressions at various grains without needing a separate pre-aggregated table for each. The trade-off is fundamentally between performance (pre-aggregation wins) and flexibility (FIXED LOD wins), analogous to the compile-time vs. runtime optimization trade-off in software engineering.

SUMMARY

Lesson Summary

Summary

FIXED LOD expressions solve the fundamental problem of computing per-entity metrics (such as per-customer total sales, per-product margin, or per-employee performance) at a grain that is independent of the visualization's level of detail. The syntax { FIXED [dimension] : AGG([measure]) } declares an absolute computation grain, producing a scalar value per unique combination of the specified dimensions. These values are then available for further aggregation (AVG, MAX, COUNTD, etc.) at whatever grain the visualization requires.

Key architectural considerations include the order of operations (FIXED evaluates before dimension filters, requiring context filters for filter interaction), the distinction from INCLUDE and EXCLUDE (which are relative to the viz grain rather than absolute), and the outer aggregation requirement when the FIXED grain is finer than the viz grain. Advanced patterns—cohort assignment, nested FIXED, boolean bucketing, and percent-of-entity calculations—extend this foundation to handle complex, real-world analytics scenarios.