An additive measure, such as qty or gross_amount, sums along every dimension. A semi-additive measure is a balance (stock on hand, customers on file): it sums across some dimensions but not over time, where you take the last or average value. A non-additive measure, such as a unit price or a ratio, never sums:
-- Non-additive: a sum of prices means nothing; rebuild ratios from additive parts
SELECT sum(unit_price) AS summed_prices, sum(gross_amount) AS gross,
round(sum(gross_amount) / sum(qty), 2) AS revenue_per_copy
FROM mart.sales WHERE status <> 'cancelled';
-- Semi-additive: customers on file is a balance; add it across countries, not over time
WITH snap AS (
SELECT q.quarter_end, count(*) AS customers
FROM (VALUES (date '2025-03-31'), ('2025-06-30'), ('2025-09-30'), ('2025-12-31'))
AS q(quarter_end)
JOIN customers c ON c.signup_date <= q.quarter_end
GROUP BY q.quarter_end)
SELECT sum(customers) AS summed_over_2025,
(array_agg(customers ORDER BY quarter_end DESC))[1] AS at_2025_year_end
FROM snap;Output
summed_prices | gross | revenue_per_copy
---------------+------------+------------------
2801628.62 | 3303427.30 | 21.56
summed_over_2025 | at_2025_year_end
------------------+------------------
19649 | 4996The summed prices look like revenue but ignore quantities, so store the additive parts (gross_amount, qty) and divide sums at query time; never average a stored ratio. BookNest never had 19,649 customers: the balance at year end was 4,996. BI tools sum numeric columns by default, so mark semi-additive measures in the semantic layer (Performance and Semantics).