Skip to content
Tutorials Center
My learning

Topics

Lesson 15 of 35 ยท Calculations

Tableau LOD Expressions: FIXED, INCLUDE and EXCLUDE Explained

Master Tableau Level of Detail expressions: FIXED, INCLUDE, EXCLUDE and table-scoped LODs, with real use cases like first purchase date, customer averages and percent of total.

The numbers in a Tableau view are normally computed at the level of the dimensions on the shelves. Put Region on Rows and SUM(Sales) shows sales per region. That is convenient, but sometimes you need a value at a different level: the sales of the whole company next to each region, the first order date of each customer, the average of customer totals. Level of Detail (LOD) expressions let you say which dimensions to aggregate by, independent of the view.

The code below is Tableau calculation syntax (read-only); pandas stand-ins reproduce each result so you can check your understanding.

The syntax

TEXT
{ FIXED [Dim1], [Dim2] : AGG([Measure]) }
{ INCLUDE [Dim] : AGG([Measure]) }
{ EXCLUDE [Dim] : AGG([Measure]) }
{ AGG([Measure]) }                       // table-scoped: whole table
  • FIXED computes at exactly the listed dimensions, whatever is in the view.
  • INCLUDE computes at the view's level plus the listed dimensions.
  • EXCLUDE computes at the view's level minus the listed dimensions.
  • A table-scoped expression with no dimensions ({ SUM([Sales]) } or { FIXED : SUM([Sales]) }) aggregates everything.

The result is a value attached to each mark, so you can compare it to other values or aggregate it again.

FIXED

Exact level Only the dimensions you list
Ignores the view Except context and data source filters

Use it for customer-level or company-level numbers.

1 / 3

FIXED: a value at a set level

Example 1: first purchase date per customer.

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

For every row of a customer, this returns that customer's first order date. Use it as a cohort field (YEAR of the first purchase) next to the order date to build retention views.

Example 2: customer lifetime value, then bins.

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

Put that on a histogram (a bin on the LOD) and you get the distribution of customer spending, even though the data source has one row per order.

Example 3: a company total beside each region.

TEXT
{ FIXED : SUM([Sales]) }

Then SUM([Sales]) / SUM({ FIXED : SUM([Sales]) }) is each region's share of the whole company, even when the view is filtered by Category.

Python Stand-in: FIXED by customer, and a total beside each row

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

A subtle point: customer_total repeats on every row of a customer. If you SUM it in a view, customer A is counted twice. Likewise AVG({ FIXED [Customer] : SUM([Sales]) }) over order rows is weighted by how many orders each customer has. The last line of the stand-in shows the right "average per customer": sum per customer first, then average those totals. In Tableau, put Customer ID on Detail so that each customer is one mark before you average.

INCLUDE: add detail, then aggregate back

Example: average of per-order totals in each region. The data has one row per order line. AVG([Sales]) gives the average line. To get the average order, first sum by order, then average:

TEXT
AVG({ INCLUDE [Order ID] : SUM([Sales]) })

The view is at Region level; INCLUDE adds Order ID, sums per order, and AVG averages those order totals within each region.

Python Stand-in: average line versus average order (INCLUDE)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

East's average line is about 168, while its average order is 252.5, because order 1 has two lines.

EXCLUDE: go up a level

Example: each sub-category's share of its category.

With Category and Sub-Category on Rows:

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

EXCLUDE removes Sub-Category from the level of detail, so the inner SUM is the category total, repeated beside each sub-category. The ratio is the share of the parent. You could also use { FIXED [Category] : SUM([Sales]) } for the same answer; EXCLUDE adapts when the view changes, while FIXED stays pinned.

Python Stand-in: share of the parent (EXCLUDE)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

FIXED versus filters: the order of operations

Here is the rule that causes most surprises:

  • A FIXED LOD is computed before ordinary dimension filters. A filter on Region does not change { FIXED : SUM([Sales]) }; the company total stays the company total. This is often exactly what you want for "percent of total".
  • To make a FIXED LOD respect a filter, turn the filter into a context filter (right-click the filter pill > Add to Context), or use INCLUDE/EXCLUDE (computed after dimension filters), or put the dimension in the FIXED list.
  • Data source and extract filters always apply, and so do context filters.

See Filters and the order of operations for the complete order.

LOD or table calculation?

Need Better tool
A value at a level that is not in the view (customer first date) LOD
A running total, rank or moving average along the view Table calculation
Percent of a parent that should ignore other filters FIXED LOD
Percent of the visible total Table calculation (Percent of Total)
Use the result as a dimension (bins, groups) LOD (FIXED can be a dimension)

LODs run in the database, so they can slow big data sources; they work as dimensions or measures, and aggregates of LODs follow the rules in Calculated fields. Table calculations are cheaper, but cannot become dimensions.

Common mistakes

  • Summing a FIXED value that repeats on every row, which double-counts. Aggregate with MIN, MAX or AVG (or ATTR) when the value is constant within the group.
  • Forgetting that FIXED ignores filters, so numbers do not move when users filter.
  • Using FIXED where the level should follow the view. EXCLUDE/INCLUDE adapt.
  • Writing the aggregation outside the braces incorrectly: { FIXED [A] : SUM([B]) } needs the aggregation inside.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does { FIXED [Customer ID] : MIN([Order Date]) } return?

    FIXED computes MIN of the order date at customer level and attaches it to each row of the customer.
  2. 2. Which LOD type computes at the view's level minus a dimension?

    EXCLUDE removes the listed dimension from the level of the view, for example to get a parent total.
  3. 3. A dimension filter on Region does not change your FIXED LOD. Why?

    FIXED LODs come before ordinary dimension filters in the order of operations. Use a context filter to make them respect it.
  4. 4. You need the average order value in each region when the data is at order-line level. Which expression?

    INCLUDE [Order ID] sums each order first and AVG then averages those order totals per region.

Summary

  • LOD expressions compute at a level you choose: FIXED (exact), INCLUDE (view plus) and EXCLUDE (view minus).
  • Classic uses: first purchase date, customer totals, order averages, percent of parent or company.
  • FIXED ignores dimension filters unless they are in context; INCLUDE and EXCLUDE follow them.
  • Do not SUM a repeated FIXED value; pick an aggregation that matches its level.

Next: Filters and the order of operations