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

"The import worked, but the year-end total is four times too big." If you have built anything in SAP Analytics Cloud, you have hit this — and the cause is almost always the same: the wrong aggregation on a measure. Aggregation is the rule SAC uses to roll your numbers up across time and hierarchies, and the default (SUM) is right for revenue and wrong for almost everything that is a balance or a rate. This guide explains SUM, AVERAGE, LAST and COUNT, when to use each, and the exception that catches everyone: aggregating over the time dimension.

What aggregation means in SAC

When you store data at a detailed level — say, monthly figures per entity — and then look at a quarter, a year, or "all entities", SAC has to combine the detail into a single number. Aggregation is the rule for that combination. You set it per measure, and it applies whenever the model rolls values up a hierarchy: month to quarter to year, entity to region to total. Choose the rule that matches the nature of the number, not just whatever the import suggested.

SUM — for additive quantities

SUM adds the values together, and it is correct for anything that genuinely accumulates: revenue, costs, units sold, headcount hires, CO2 emissions, cash inflows. Twelve months of revenue summed gives the annual revenue; that is what you want. SUM is the default in SAC, which is why additive measures usually just work — and why everything else usually doesn't.

AVERAGE — for rates and ratios across members (carefully)

AVERAGE returns the mean of the values. It is tempting for things like an average price or an average headcount, but use it with caution: averaging an already-derived rate across periods often produces a figure that is subtly wrong, because a simple average ignores the weights behind each period. As a rule, a true ratio (margin %, conversion rate, cost per unit) should not be aggregated at all — it should be a calculated measure computed from its numerator and denominator at every level. Reserve AVERAGE for genuinely averageable quantities, and reach for a calculated measure whenever the number is a ratio.

LAST — for balances and stock levels

This is the one that prevents the "four times too big" disaster. A balance — a bank balance, inventory on hand, a capital figure, total headcount at a point in time — must not be summed across time. The year-end inventory is not January's stock plus February's stock plus… it is simply the closing value. For these, set the aggregation to LAST (the value of the last period in the range). LAST across the time dimension gives the closing balance; SUM would multiply it nonsensically. Any measure that represents a level at a moment rather than a flow over a period wants LAST.

COUNT — for counting occurrences

COUNT returns the number of entries rather than their total. It is useful for things like the number of transactions, number of active customers, or number of open tickets, where you care how many records exist, not their summed value. It is the least-used of the four but exactly right when the question is "how many", not "how much".

The exception everyone trips on: the time dimension

Here is the subtlety that catches even experienced users. A measure can aggregate one way across regular dimensions and a different way across time. Take headcount: across entities it is additive (Paris 50 + London 30 = 80 people), so it sums; but across time it is a balance (you do not add January's 80 to February's 82 to get 162), so over time it must be LAST. SAC lets you set a separate time-dependent aggregation — typically SUM across entities and LAST across periods — and getting this combination right is the difference between a model that reconciles and one that quietly lies. If a measure is a balance, check its time aggregation specifically, not just the default.

A quick decision guide

• Does it accumulate (revenue, units, costs, emissions)? → SUM.
• Is it a balance or stock at a point in time (inventory, headcount, capital)? → LAST over time.
• Is it a ratio or rate (margin %, price, cost per unit)? → calculated measure, not an aggregation.
• Are you counting records (transactions, customers)? → COUNT.
• Is it a genuinely averageable quantity with equal weights? → AVERAGE, with care.

Where this fits

Aggregation is the detail that decides whether your imported figures are right, so it pairs directly with two other fundamentals: structuring the model with the Version dimension (Actual / Budget / Forecast), and getting the data in cleanly via a multi-sheet Excel import or a CSV import. Our ready-made templates ship with the aggregations already set correctly on every measure, so you skip this class of error entirely. Not sure which one fits your case? Let the assistant recommend one.