Additivity comes first

Which measures can be summed across which dimensions, and why must ratios be recomputed rather than averaged?

CaseConstructed case · Nadia at Fernwood Outfitters

Average margin 30%, or 14%?

Marketing's dashboard shows an average margin of 30% for the first two days of a promotion. Finance says 14%. Both use the same two rows:

day	revenue	margin (profit)	margin %
Mon	100	50	50%
Tue	900	90	10%

Marketing's dashboard averages the margin % column: (50% + 10%) / 2 = 30%. Finance divides total profit by total revenue: (50 + 90) / (100 + 900) = 140 / 1000 = 14%. Nobody wrote a wrong query in the usual sense. On the same dashboard, the "gift-card balance" tile for the year shows $18M, but the company has never owed more than $1.5M. Nadia must find out why, and which fact table design the data really needs.

How it ends →

Additivity comes before fact table types, because it derives the types and because it is the most common silent error in the field.

A measure is:

Kind	Meaning	Examples
Additive	Summing it across every dimension is meaningful	sale amount, quantity shipped
Semi-additive	Summing across some dimensions is meaningful, but not across time	account balance, inventory on hand, headcount
Non-additive	It cannot be summed at all	ratios, percentages, unit prices, margins

Why semi-additive measures cannot be summed over time

The reason is not convention. These measures are stocks: a quantity that persists and is measured at a moment (versus a flow, which is an amount that happens during a period). Summing a stock across time counts the same persisting thing once per period.

That is Nadia's gift-card tile: it summed 12 month-end balances.

Non-additive measures must be recomputed

Ratios, percentages, unit prices and margins must be recomputed from their components at the target grain: sum the numerator, sum the denominator, then divide. Never average them.

-- WRONG: average of ratios
SELECT AVG(margin_pct) FROM daily;            -- 30%

-- RIGHT: ratio of sums
SELECT SUM(margin) * 1.0 / SUM(revenue) FROM daily;   -- 140 / 1000 = 14%

RelationshipRatios do not average →

avg (a / b) \neq = Σ a /Σ b

Stated in 2 places →Principle: The algebra decides →

Averaging a ratio is the single most reliable way to produce a number that is wrong by a defensible-sounding amount. (The average gives each day the same weight, even though Tuesday had nine times the revenue.)

This is arithmetic, not a modeling convention. It is the first thing a semantic layer exists to enforce.