Non-Additive Fact

A non-additive fact is a measure that cannot be meaningfully summed along any dimension. Ratios are the Kimball Group’s example: a discount rate, a conversion rate, a margin percentage. Adding the conversion rates of two campaigns does not produce the conversion rate of both.

Other common non-additive measures include unit prices, averages, percentiles, and distinct counts. It is the opposite end of a spectrum that runs from additive facts (summable along every dimension) through semi-additive facts (summable along some).

Why averaging does not fix it

When a sum is obviously wrong, the natural next step is AVG(). For ratios this is usually also wrong, because an average of ratios weights every row equally regardless of its size. In an invented example, one order of 1,000 has a 10% discount and nine orders of 10 each have no discount. The average of the ten discount rates is 1%. The actual discount rate across all sales — 100 discounted out of 1,090 sold — is about 9.2%.

Store the parts, compute the ratio last

The Kimball Group’s prescription is specific, and conditional: where possible, store the fully additive components of the non-additive measure, sum those components into the final answer set, and only then calculate the non-additive fact — typically in the BI layer or an OLAP cube. The condition is that the components are additive across the rows being combined; check each one’s grain, population, and possible duplicates first.

A stored rate column invites someone to average it later, which is a reason to keep its components beside it and document how it aggregates. Whether to store the rate at all is a separate decision: a rate measured at the source, one fixed by contract, or one kept for row-level audit can be worth storing, used for what it means. A contracted 10% discount rate is a property of the contract, not a result to aggregate; where a report needs an overall rate across rows and the additive components exist, compute it from those components rather than from the stored rates.

Distinct counts are a special case

A distinct count has no additive components to store. A customer active on three days appears in each day’s distinct count, so summing daily active customers overcounts monthly active customers by every repeat visit. Rolling a distinct count up to a coarser level requires the underlying identifiers, a combinable representation of them — an exact set or bitmap of identifiers that can be unioned, or an approximate sketch such as HyperLogLog that merges within a known error — or a count computed directly at the level being reported. Precomputed distinct counts alone cannot in general be combined into a correct coarser count; the exception is sets known not to overlap, such as days with no customer in common. A distinct-user conversion rate inherits the same limit, so its numerator and denominator have to be rebuilt the same way.

Where non-additive measures appear in reports, record the rule that computes them with the metric definition so that every consumer applies the same one. How additivity interacts with grain and double counting is worked through in Grain, Facts, and the Arithmetic That Quietly Goes Wrong.

References: Kimball Group, Additive, Semi-Additive, Non-Additive Facts; Snowflake Documentation, Computing the Number of Distinct Values.