FIXED, INCLUDE, and EXCLUDE — In Depth

With the three LOD keywords understood individually, this lesson builds the patterns that appear in professional dashboards: customer segmentation, cohort analysis, ratio-to-parent calculations, and the rules that govern how LOD expressions interact with filters and aggregations.

FIXED with Multiple Dimensions

A FIXED expression can declare more than one Dimension, separated by commas. The aggregation then computes at the intersection of all declared Dimensions — just as a view-level aggregation computes at the intersection of all Dimensions currently in the view.

Sales per Customer per Region — two-Dimension FIXED

{ FIXED [Customer Name], [Region] : SUM([Sales]) }

FIXED with no Dimension — grand total on every row

{ FIXED : SUM([Sales]) }

Cohort Analysis — FIXED in Practice

Cohort analysis groups customers by the month they first purchased and then measures their behaviour in subsequent months. It requires two FIXED expressions working together — the acquisition date and the months-since-acquisition offset — both attached to every order row.

Step 1 — Acquisition Month per Customer

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

Step 2 — Months Since Acquisition per Order

DATEDIFF('month',
  { FIXED [Customer Name] : MIN([Order Date]) },
  [Order Date]
)

Cohort Grid — Labelled Mockup

INCLUDE — Computing at a Finer Granularity Than the View

INCLUDE temporarily adds the Dimension you declare to the current view's Dimensions, computes the aggregation at that finer level, and then lets the outer aggregation summarise those finer results back up to the view level. The key phrase is finer than the view — INCLUDE always drills down, never up.

The most common situation where INCLUDE is needed: you want the average of a group total, not the average of individual rows. These are very different numbers and a plain AVG() cannot distinguish between them without INCLUDE.

Average Customer Spend per Category — INCLUDE in practice

AVG( { INCLUDE [Customer Name] : SUM([Sales]) } )

Maximum single-order value per Sub-Category — INCLUDE with MAX

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

INCLUDE — How the Two-Step Evaluation Works

INCLUDE vs FIXED — Choosing Between Them

Customer Segmentation — FIXED for Bin Assignment

A common segmentation pattern computes a customer-level metric with FIXED and then uses an IF/CASE calculation to assign each customer to a segment. Because the FIXED value is the same on every row for a given customer, the segment label is also consistent across all their rows — making it safe to use as a filter or Color Dimension without double-counting.

Customer Lifetime Segment — combining FIXED with IF logic

IF { FIXED [Customer Name] : SUM([Sales]) } >= 5000 THEN "High Value"
ELSEIF { FIXED [Customer Name] : SUM([Sales]) } >= 1000 THEN "Mid Value"
ELSE "Low Value"
END

Ratio to Parent — EXCLUDE for Share of Category

A ratio-to-parent calculation divides each Sub-Category's Sales by its parent Category's total. EXCLUDE removes Sub-Category from the aggregation to get the Category-level denominator — without leaving the current view or adding a separate table calculation.

Sub-Category Share of Category Sales

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

LOD Expressions in the Filter Order

Understanding where LOD expressions sit in the filter execution order explains nearly every unexpected result when LOD and filters interact.

Nested LOD Expressions

LOD expressions can be nested inside other LOD expressions or inside regular aggregation functions. A nested LOD first computes the inner expression at its declared granularity, then the outer expression aggregates those results at its own level. The cohort months-since-acquisition calculation from earlier is an example — DATEDIFF() wrapping a FIXED expression.

Average Customer Lifetime Value by Region — nested FIXED inside AVG

AVG( { FIXED [Customer Name] : SUM([Sales]) } )

Next up — Lesson 40: Advanced LOD patterns — set-based analysis, survey data normalisation, and using LOD expressions to solve problems that require multiple levels of aggregation simultaneously.