Classifying Measures and Defining Aggregation Rules

Hello! In the previous lesson, you chose a fact-table pattern by asking what one row records: an event, a scheduled snapshot, or a case moving through a workflow. That choice does not tell you how to total its numeric columns. An invoice-line amount and an end-of-day inventory balance can both be valid facts, yet summing one across dates is useful while summing the other is misleading.

In this lesson, you will classify measures by where summation is valid, then turn each classification into an aggregation rule that a SQL query or BI model can enforce. Allow about 40 minutes: roughly 10 minutes for the two short readings and 30 minutes for the examples and query walkthrough.

---

Classify the measurement, not the table

The useful question is not “Is this a number?” but “What would the sum of these rows mean?” A numeric column can hold an amount earned, a position observed, or a rate calculated. Those meanings impose different rules even when the columns sit in the same fact table.

Read the Basic Fact Table Techniques portion of the Kimball Group’s Kimball Dimensional Modeling Techniques. It places numeric measures in the context of the fact row’s measurement event, then defines the three additivity classes.

Kimball’s definitions concern summation across dimensions:

• Additive: You can sum the measure across every relevant dimension without changing its meaning.
• Semi-additive: You can sum it across some dimensions but not others.
• Non-additive: Summing the measure across dimension members does not produce a meaningful total.

These are rules for SUM, not declarations that AVG, MIN, MAX, or COUNT are forbidden. Those functions answer different questions and must be judged separately.

---

Additive: amounts attributable to distinct events

Return to an invoice-line transaction fact. Suppose each row has quantity_sold, gross_amount, and discount_amount, where gross_amount means the extended amount before discount. If the lines do not overlap, their amounts can be totaled by day, product, customer, or any combination of those dimensions. Summing the lines for a month gives that month’s gross billed amount; summing them by product gives the product’s gross billed amount.

For example, two lines have these values:

Line	Quantity	Unit price	Gross amount	Discount amount
A	2	10	20	2
B	8	5	40	8
Total	10	—	60	10

The total quantity is 10 items; the total gross amount is 60 currency units; the total discount is 10. Each total describes the same set of non-overlapping invoice lines. Net amount, if defined as gross less discount, is 50.

Additivity has preconditions. Amounts must refer to a compatible unit, currency, and business definition. Adding 30 units of one product to 20 units of another can mean “50 individual items” if both quantities count individual items; adding 30 cases to 20 individual items does not yield a defensible quantity without conversion. Likewise, 60 in one currency plus 40 in another is not a 100-unit revenue total. Convert using an agreed exchange-rate policy, or group and report the currencies separately.

This leads to a practical way to document gross_amount:

Valid SUM: Across invoice dates, products, customers, and other dimensions, provided rows represent distinct invoice lines and amounts share the specified reporting currency.

The qualification matters. “Sales revenue is additive” is a useful shorthand, but it is not permission to disregard currency, duplicate lines, or the measure’s business definition.

---

Semi-additive: a position at a point in time

Now consider the periodic snapshot from the previous lesson:

Each row records the end-of-day stock position for one product in one store on one calendar date.

Suppose the same product has 12 units at Monday’s close and 9 at Tuesday’s close. Summing those rows gives 21 unit-observations, not 21 units currently on hand. The second row reports a later position, rather than additional stock to add to the first.

At Monday’s close, however, you might add that product’s balances across two stores—say 12 and 7—to report 19 units on hand across those stores at Monday’s close. stock_on_hand is therefore semi-additive: it can be summed over compatible products or locations when that total answers a defined question, but not across snapshot dates. If products use different units, or locations represent overlapping holdings, even the cross-product or cross-location total needs adjustment.

Its aggregation contract could read:

Valid SUM: Across non-overlapping stores for a specified product and snapshot date; across products only when quantities have compatible units and “total items” is a meaningful metric. Invalid SUM: Across snapshot dates. For an end-of-month position, select the appropriate month-end snapshot; for an average daily position, average the intended daily observations under an explicit missing-day policy.

Notice what AVG means here. If you have one equally spaced end-of-day observation for every day, their arithmetic average is an average of daily closing balances. If some days are missing, a simple AVG silently excludes them. You must decide whether a missing day means no observation, zero stock, or a balance that should be carried forward. With irregularly spaced observations, an arithmetic average of rows may not represent a time-weighted average.

A snapshot table need not contain only semi-additive measures. The same product-store-day row might also store units_received_during_day. Those receipts represent activity during each day, so—assuming distinct, consistently defined periods—you can sum them across days. The table pattern describes when rows are observed; each measure still needs its own aggregation rule.

Read Microsoft Learn’s Modeling Fact Tables in Warehouse, “Measure types,” for a second account of these categories, including its currency and temperature examples.

In particular, a blanket rule such as “every measure in a periodic snapshot cannot be summed over time” is too broad: closing balance cannot, but receipts during the day can. Similarly, whether stock can be added across products depends on its unit and the question being asked.

---

Non-additive: calculate rates after aggregation

Consider the unit_price values of 10 and 5 in the invoice example. Their sum, 15, is neither a price charged on a line nor a meaningful overall unit price. Even their unweighted average, 7.5, ignores that line A sold two units and line B sold eight.

For these lines, the effective price per unit is defined from the aggregated components:

$$\text{Effective price per unit}=\frac{\sum \text{gross amount}}{\sum \text{quantity sold}}=\frac{60}{10}=6.$$

A unit price is non-additive. A simple AVG(unit_price) can still be valid if the question specifically asks for the average price recorded per line, with each line weighted equally. It is not the answer to “What was the average price paid per item?” Those are different metrics.

The same principle applies to percentages. Line A’s discount rate is $2/20=10\%$, and line B’s is $8/40=20\%$. Summing the percentages gives 30%, which is meaningless as a combined discount rate. Their unweighted average, 15%, describes the average line-level rate, not the overall share of gross value discounted. For the latter:

$$\text{Overall discount rate}=\frac{\sum \text{discount amount}}{\sum \text{gross amount}}=\frac{10}{60}\approx 16.67\%.$$

The denominator here is pre-discount gross amount by definition. Replacing it with net amount would produce a different metric. The valid rule is therefore precise:

Discount rate: Do not sum line rates. For an overall rate, sum discount amounts and pre-discount gross amounts over the same eligible lines, then divide. Define behavior when the summed denominator is zero.

Margin percentages follow the same pattern: sum the appropriate profit and revenue components first, then calculate the percentage according to the agreed margin definition. Temperature readings are another non-additive example: you may report a minimum, maximum, or suitably defined average, but their sum is not a temperature of the group.

The distinction is “non-additive” does not mean “unanalyzable.” It means SUM is not its roll-up operator. MIN and MAX can describe extremes; AVG can describe an average of observations when its weighting is appropriate; COUNT can describe the number of recorded observations. None of these automatically replaces a carefully defined overall rate.

---

Put aggregation rules where queries can use them

With your SQL background, the difference between averaging stored rates and calculating an overall rate is easy to make concrete. Suppose fact_invoice_line contains one row per issued line, and its monetary amounts are already expressed in a common reporting currency. A grouped PostgreSQL query could use the following pattern:

SELECT
    d.calendar_year,
    d.calendar_month,
    p.category,
    SUM(f.quantity_sold) AS units_sold,
    SUM(f.gross_amount) AS gross_amount,
    SUM(f.discount_amount) AS discount_amount,
    SUM(f.discount_amount)::numeric
        / NULLIF(SUM(f.gross_amount), 0) AS overall_discount_rate,
    SUM(f.gross_amount)::numeric
        / NULLIF(SUM(f.quantity_sold), 0) AS effective_unit_price
FROM fact_invoice_line AS f
JOIN dim_date AS d
    ON d.date_key = f.invoice_date_key
JOIN dim_product AS p
    ON p.product_key = f.product_key
GROUP BY
    d.calendar_year,
    d.calendar_month,
    p.category;

NULLIF makes a zero denominator produce a null result rather than a division error; whether null, zero, or “not applicable” should be displayed is a business-facing decision. More importantly, both divisions occur after their additive components have been summed for the selected month and category. SUM(f.unit_price) and SUM(f.discount_rate) would not be suitable replacements.

Before relying on such a query, check three things:

1. Row population: Are these distinct invoice lines, with credits or cancellations represented according to the intended revenue definition?
2. Unit and currency: Do all rows being combined use compatible quantities and the same reporting currency?
3. Join cardinality: Does each fact row join to exactly one selected date row and one selected product row? An unintended one-to-many join duplicates amounts before aggregation, even when the measures themselves are additive.

A compact measure specification makes these constraints available beyond one query:

Measure	Class	Valid roll-up rule
Invoice-line gross amount	Additive, within one currency basis	SUM over distinct lines and relevant dimensions
Invoice-line discount amount	Additive, within one currency basis	SUM over the same eligible lines as gross amount
Quantity sold	Additive for compatible units	SUM; convert or separate unlike units
End-of-day stock on hand	Semi-additive	SUM across compatible, non-overlapping entities at one snapshot time; select or average snapshots for time questions
Unit price	Non-additive	Derive an effective unit price from total amount and total units; use AVG only for an explicitly defined average of recorded prices
Discount percentage	Non-additive	Divide summed discount by summed eligible pre-discount amount
Temperature reading	Non-additive	Use an appropriately defined MIN, MAX, or average, not SUM

The classification belongs in metric documentation or the BI semantic model, alongside the measure’s grain, currency or unit, numerator and denominator if applicable, and treatment of missing observations. Otherwise, a perfectly valid numeric column can invite a perfectly invalid total.

---

Conclusion

Additive measures represent compatible amounts that can be summed across the fact’s dimensions. Semi-additive measures, such as closing balances, permit sums only along specified dimensions—typically not across observation times. Non-additive measures, such as prices, ratios, and temperatures, need a different operation or a calculation from aggregated components. In every case, the valid rule depends on the business question, the row grain, and the units being combined.

You now have a way to decide whether an individual measure rolls up correctly. Next, you will apply the grain test to collections of proposed measurements, detecting when they cannot coherently live in the same fact table at all.