Browse
Calculated Measures & Aggregations
Writing correct aggregations and calculated measures — where subtle mistakes silently produce wrong numbers.
What it is
Calculated measures define how raw data is aggregated into a reportable metric (e.g. "average order value" as a ratio of two sums, not an average of ratios) — a place where small logical mistakes produce numbers that look plausible but are quietly wrong.
Key points
- Ratio of sums vs. average of ratios: a classic pitfall — "average conversion rate across regions" computed as the average of each region's rate gives a different (usually wrong) answer than total conversions divided by total visits.
- Context-dependent aggregation: a measure like "active users" needs a precisely defined time window and definition of "active" — the same word means different things across different reports without an explicit, shared definition.
- Row-level vs. aggregated calculations: some logic must be computed per-row before aggregating (e.g. a discount applied per line item); computing it after aggregating produces a different, incorrect result.
- Calculated measures are where a BI developer's SQL/DAX correctness directly determines whether the business is making decisions on accurate numbers — this is a high-stakes correctness surface, not just a formatting detail.