Semi-Additive Fact

A semi-additive fact is a measure that can be summed across some dimensions of a fact table but not across others. The Kimball Group’s definition is exactly that — it “can be summed across some dimensions, but not all” — and its standard example is an account balance, which is additive across every dimension except time.

Why the time dimension is different

A balance describes a state at a moment, not an amount that happened during a period. Adding the balances of all accounts on 31 March gives the total held on 31 March, which is meaningful. Adding one account’s balance on 31 January, 28 February, and 31 March gives a number that corresponds to nothing — the same money counted three times.

The same pattern appears in any measure of level rather than flow: inventory on hand, headcount, open support tickets, active subscriptions. These usually live in periodic snapshot tables, where each row records the state of something at the end of a period.

Aggregating it correctly

Rolling a semi-additive fact up over time needs a decision, and both common choices are legitimate:

• Point in time — the balance at the last day of the quarter. Answers “what did we hold at quarter end?”
• Average over the period — the mean daily balance. Answers “what did we typically hold?” and requires a clear denominator: the average over every day in the period, including days with no activity, is not the average over days that happen to have rows.

-- Quarter-end total balance: sum across accounts, but only on the last day
SELECT SUM(balance)
FROM account_daily_snapshot
WHERE snapshot_date = DATE '2026-03-31';

-- Average daily total balance over the quarter
SELECT AVG(daily_total)
FROM (
    SELECT snapshot_date, SUM(balance) AS daily_total
    FROM account_daily_snapshot
    WHERE snapshot_date BETWEEN DATE '2026-01-01' AND DATE '2026-03-31'
    GROUP BY snapshot_date
) t;

The second query assumes the snapshot has a row for every account on every day, which is what a periodic snapshot is designed to provide.

How it goes wrong

The failure is silent and confident. If a BI tool sums numeric columns by default, dragging “balance” onto a monthly chart produces a large, plausible-looking number that is meaningless. Nothing errors. The practical defenses are to record additivity where consumers will see it — “semi-additive: do not sum across time” in the column description or semantic layer — and to define the approved time aggregation once, alongside the metric definition.

A measure that cannot be summed along any dimension is a non-additive fact instead. How additivity fits 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; Kimball Group, Periodic Snapshot Fact Tables.