Measure Additivity

Additivity describes which dimensions a measure may be summed across. It is a property of what the number means, not of its data type, and a fact table that ignores it produces totals no one can defend.

Fully additive measures sum across every dimension. Revenue, quantity, and cost are additive: revenue by product, by month, by region, and the grand total are all meaningful sums of the same rows.

Semi-additive measures sum across some dimensions but not time. An account balance, an inventory level, and a headcount are levels rather than flows — adding December’s daily balances does not give the month’s balance (the sum is useful only as a step toward a period average, after dividing by the number of days). Across accounts or warehouses on a given day, the same measure sums correctly. These need a stated rule in the model: period-end, period-average, or point-in-time, because the three give different answers and each report would otherwise choose its own.

Non-additive measures sum across nothing. Ratios, percentages, averages, and unit prices fall here. Averaging stored ratios weights every group equally regardless of size, so the result generally differs from the overall ratio (it matches only in special cases such as equal denominators). That is why a margin percentage stored per row is correct at that row but usually wrong when rolled up.

The design consequence is to store the components, not the result: keep numerator and denominator as measures and divide after aggregating, so the ratio is computed at whatever level the reader groups to. Depending on its aggregation setting, a BI tool may average a stored percentage without warning, and the resulting figure is difficult to trace back to a cause.

That advice has a precondition worth spelling out, because following it mechanically produces a second, less obvious error: the components have to be additive themselves. Divide-after-aggregating fixes the ratio only if the numerator and denominator are things that can be summed over the rows you are combining.

Distinct counts are the standard counterexample. Store daily unique users as the denominator of an engagement rate and the monthly figure is wrong however carefully you divide, because summing daily uniques counts a user who visited on three days three times. Two days with sets {1,2} and {2,3} give a sum of 4 against a true monthly unique count of 3. Nothing in the aggregation can recover the overlap, since the identities are no longer in the table.

Where a component is not additive, there are two honest options and one that only looks like an option.

• Recompute from the detail at whichever grain is asked for. Correct, and the reason the detailed table has to survive the existence of the summary.
• Store something mergeable instead of a count — the set of identifiers, or a sketch that supports union with a stated error. Then combining periods is a merge rather than a sum.
• Sum the daily counts and label it. Not a unique count at all; it is daily-uniques-summed, which is a legitimate measure of a different thing and a misleading one under the name “users.”

Even for amounts, summability is an assumption rather than a given. Rows have to share a unit and a currency — mixed currencies or a column holding both cents and dollars add to a number with no meaning — and the grain has to be free of duplicates, since a fact table that repeats a row after a fan-out join sums to more than the business did. So the question for each measure is not only is this a ratio but over which dimensions can these rows be added at all, and that answer belongs in the model rather than in each report.

How additivity decides which arithmetic a fact table supports is worked through in Grain, Facts, and the Arithmetic That Quietly Goes Wrong.