Y

Tableau · Intermediate

Level of detail expressions

2 min read

Why it matters

Most Tableau questions that stump candidates need a calculation at a different level of detail than the view: "average revenue per customer by country" or "each customer's first order month". Level of detail (LOD) expressions answer these. They are to Tableau what CALCULATE is to Power BI, and interviewers use them to judge depth.

The concept

A view has a level of detail set by the dimensions in it. LOD expressions compute at another level:

  • { FIXED [Customer ID] : MIN([InvoiceDate]) } computes per customer, ignoring the view's dimensions (but respecting context and data source filters).

  • { INCLUDE [Customer ID] : SUM([Revenue]) } computes at the view's level plus customer.

  • { EXCLUDE [Country] : SUM([Revenue]) } computes at the view's level minus country.

Aggregate the result again in the view, for example AVG of an INCLUDE expression.

Worked example: customers on the Online Retail data

Average revenue per customer by country. A plain SUM(Revenue) / COUNTD([Customer ID]) works, but the INCLUDE version generalises to medians and distributions:

Customer
{ INCLUDE [Customer ID] : SUM([Revenue]) }

Put Country on Rows and AVG([Customer Revenue (INCLUDE)]) on Columns. The UK shows about 1,864 per customer; the Netherlands about 31,716 with only 9 customers. Switch the aggregation to MEDIAN to see how much a few big buyers drive those averages.

Cohort month per customer:

Cohort
DATETRUNC('month', { FIXED [Customer ID] : MIN([InvoiceDate]) })

Drag Cohort Month to Rows and COUNTD([Customer ID]) to Columns to see how many new customers arrived each month. Combine with a months-since-first calculation for a cohort heatmap:

Months
DATEDIFF('month', [Cohort Month], DATETRUNC('month', [InvoiceDate]))

Share of total without table calculations:

Share
SUM([Revenue]) / MIN({ FIXED : SUM([Revenue]) })

Common mistakes

  • Forgetting that FIXED ignores dimension filters. A country quick filter does not change a FIXED result unless you add the filter to context.

  • Using table calculations where an LOD is needed. Table calculations work on what is in the view; if the customer is not in the view, you need an LOD.

  • Aggregating twice incorrectly. An INCLUDE expression returns a value per customer; the view then needs AVG, MEDIAN or SUM on top. Choose deliberately.

Interview angle

"Compute each customer's first purchase date and count new customers per month" is a common Tableau task. Answer with FIXED, and mention its interaction with filters. That detail is what the interviewer is listening for.

Practice task

  1. Build a cohort retention heatmap (Cohort Month by Months Since First, coloured by distinct customers).

  2. Show the median revenue per customer by country, and explain why it differs from the average.

  3. Recreate "Share of Total Revenue" with a quick table calculation and compare.

Further reading