Tableau Course
Advanced LOD Patterns
Advanced LOD patterns solve the problems that come up most often in real business dashboards — new vs returning customer counts, survey normalisation, comparing a member to its group average, and simultaneous aggregation at two different levels. Each pattern uses the LOD keywords in combinations that are not obvious from the syntax alone.
New vs Returning Customers
A new customer is one whose first order falls within the current period being shown. A returning customer has ordered before. This requires knowing each customer's first-ever order date — which comes from FIXED — and then comparing it to the order date in each row to classify the transaction.
Step 1 — First Order Date per Customer
{ FIXED [Customer Name] : MIN([Order Date]) }Step 2 — New or Returning flag per order row
IF DATETRUNC('month', [Order Date]) =
DATETRUNC('month', [First Order Date])
THEN "New"
ELSE "Returning"
ENDNew vs Returning — Stacked Bar Mockup
Comparing Each Member to Its Group Average
A common executive request: show each Sub-Category's Sales alongside the average Sales for its parent Category — so the viewer can instantly see which Sub-Categories are above or below their category's average. FIXED computes the category average once and stamps it on every Sub-Category row for comparison.
Category Average Sales — for benchmarking each Sub-Category
{ FIXED [Category] : AVG([Sales]) }Above or Below Category Average — conditional flag
IF SUM([Sales]) >= { FIXED [Category] : AVG([Sales]) }
THEN "Above Average"
ELSE "Below Average"
ENDSurvey Normalisation — INCLUDE for Per-Respondent Scoring
Survey data typically has one row per respondent per question. To compute average scores correctly, you first need the total score per respondent, then average those totals — not average individual question scores directly, which would be skewed by respondents who answered different numbers of questions. INCLUDE handles this cleanly.
Average Respondent Score by Department — INCLUDE for normalisation
AVG( { INCLUDE [Respondent ID] : SUM([Score]) } )Dual-Level Aggregation — FIXED Inside a Ratio
Some analyses require aggregating at two different levels simultaneously — for example, each product's Sales as a share of its brand's total, where both the product total and the brand total need to be computed correctly at once. A single FIXED expression inside a division handles both levels in one calculated field.
Product Share of Brand Sales — dual-level ratio
SUM([Sales]) / { FIXED [Brand] : SUM([Sales]) }LOD Pattern Reference
| Business Question | LOD Pattern | Keyword |
|---|---|---|
| Customer lifetime value | { FIXED [Customer] : SUM([Sales]) } | FIXED |
| Customer acquisition date | { FIXED [Customer] : MIN([Order Date]) } | FIXED |
| New vs returning customer flag | DATETRUNC([Order Date]) = DATETRUNC([First Order Date]) | FIXED + IF |
| Above / below group average | SUM([Sales]) >= { FIXED [Category] : AVG([Sales]) } | FIXED + IF |
| Average of group totals | AVG( { INCLUDE [Customer] : SUM([Sales]) } ) | INCLUDE |
| Normalised survey scores | AVG( { INCLUDE [RespondentID] : SUM([Score]) } ) | INCLUDE |
| Sub-category share of parent | SUM([Sales]) / { EXCLUDE [Sub-Cat] : SUM([Sales]) } | EXCLUDE |
| Grand total denominator | { FIXED : SUM([Sales]) } | FIXED |
The new vs returning customer pattern is one of the most requested analyses in growth dashboards — and the only way to build it correctly in Tableau is with FIXED. The key insight is that "new" is a property of the customer, not the order. A customer who first ordered in January is "new" for all their January orders and "returning" for every subsequent order. FIXED stamps that first-order month on every row, and then the IF comparison classifies each order against it. The above/below group average pattern appears constantly in sales performance reviews. Stakeholders want to know which products, regions, or salespeople are outperforming their peer group — not the overall average. FIXED [Category] : AVG([Sales]) gives each Sub-Category its own category-specific benchmark. Pair this with conditional colour — green for above, red for below — and the underperformers stand out immediately. The LOD pattern reference table in this lesson is worth bookmarking. Every LOD problem in practice maps to one of those eight patterns. When you encounter a calculation that feels like it needs two levels of aggregation at once, check the table first — the pattern and keyword are almost certainly already there.
Practice Questions
1. Write the full two-part calculation that flags each order row as New or Returning — where New means the order falls in the same month as the customer's first-ever order.
2. Write the calculated field that labels each Sub-Category as Above Average or Below Average — compared to its own parent Category's average Sales, not the overall grand average.
3. Survey data has one row per respondent per question. Write the LOD expression that returns the average total score per respondent at Department level — not the average of individual question scores.
Quiz
1. A new vs returning customer flag requires knowing each customer's first-ever order date on every one of their order rows. Which LOD keyword correctly provides this, and why?
2. A view shows individual Products with their SUM(Sales). You need each product's Sales as a share of its Brand's total Sales — not the grand total. Which formula achieves this?
3. A survey dataset has one row per respondent per question. What is the difference between AVG([Score]) and AVG({ INCLUDE [Respondent ID] : SUM([Score]) }) when the view shows Department?
Next up — Lesson 41: Dashboard basics — assembling worksheets into a dashboard, controlling layout, sizing, and adding text and image objects.