SUM, AVERAGE, LAST, COUNT: choosing the right aggregation in SAC

Most wrong figures in a SAC Story come from a badly chosen aggregation. SAC suggests SUM by default, but that setting is only correct for some measures. Here is the rule.

SUM: amounts and volumes

Addition suits cumulative quantities: revenue, units sold, costs, number of tickets. If the sum of a measure across all dimensions still makes sense, SUM is the right choice.

AVERAGE: rates and percentages

A conversion rate, a percentage margin, an occupancy rate do not add up. Summing a 3% rate over a hundred rows would give 300%, which is absurd. Choose AVERAGE — or better, recompute the rate from its components (aggregated numerator ÷ aggregated denominator) for a properly weighted average.

LAST: stocks and balances

A stock level, a cash balance, a number of active subscribers is a snapshot at a given moment. On the time dimension, you must not add the twelve months: you take the last value. SAC handles this with an aggregation exception: aggregation stays SUM on normal dimensions but becomes LAST on time.

COUNT: counting

To count distinct occurrences — number of active customers, number of SKUs — use COUNT or COUNT (distinct) rather than a sum of identifiers.

Set the aggregation exception

In the Modeler, open the measure, then set its default aggregation and, if needed, its exception on the Date dimension. Setting this once and for all avoids hours of debugging wrong totals in Stories.

Templates already set up

Every model in the SAC Templates Hub catalog ships its measures with the right aggregation — SUM for amounts, AVERAGE for rates, LAST for stocks. You inherit these settings instead of rediscovering them.