A stored margin_pct column in a fact table sums to 812% on a dashboard — how do you fix the model?
answer
- what does adding two percentages mean?
- each row's denominator is different
- is AVG really the business answer?
- store which two columns instead?
- divide after the sums, not before
basics
~20 sPercentages and ratios are non-additive: they cannot be summed across any dimension. Store the fully additive numerator and denominator in the fact row instead, and compute the ratio at query time as sum of numerator over sum of denominator.
solid answer
~50 sA percentage is a ratio whose denominator differs per row, so adding rows adds nothing meaningful — the 812% is simply the arithmetic sum of many small percentages. Averaging them is only marginally better: an unweighted average treats a $2 order line the same as a $200,000 one. The fix is a modelling one. Store the **additive components** in the fact table — `revenue_amount` and `cost_amount` — and derive margin as `SUM(revenue) - SUM(cost)) / SUM(revenue)` wherever it is consumed. That aggregates correctly at every grain, from one order line to the whole year, and is automatically weighted by size. Then stop the wrong query from being possible: remove or rename the stored column, and define margin as a **calculated measure in the semantic layer or published view** so ad-hoc tools cannot drag a percentage into a SUM. If the ratio must be materialised for performance, materialise the components at the coarser grain and divide there.
code
text · 8 linesline revenue cost margin_pct
A 100.00 60.00 40.0
B 2.00 1.00 50.0
C 20000.00 19000.00 5.0
SUM(margin_pct) = 95.0 <- meaningless
AVG(margin_pct) = 31.7 <- unweighted, wrong
(20102-19061)/20102 = 5.18% <- correct, revenue-weightedgo deeper
Recall that percentages and ratios cannot be summed, and that the fact table should store the numerator and denominator so the ratio is computed afterwards.
Explain why the average is not the fix either — each row has its own denominator, so an unweighted mean ignores row size — and write the post-aggregation expression with a divide-by-zero guard.
Show that you fix the model and the consumption path: remove or rename the column, define the measure once in a view or semantic layer, add a test, and manage the correction with the consumers of the old number.
Own metric definition as a governance problem: who defines a published measure, how disagreements between dashboards are prevented, and how a wrong number already in circulation is corrected without destroying trust.
## Why ratios do not aggregate Measures come in three additivity grades: additive (summable across everything), semi-additive (summable across everything except time), and **non-additive** (summable across nothing). Ratios, percentages, unit prices, precomputed averages, and distinct counts are all non-additive. The reason is structural. A ratio stored on a row is a quotient with a row-specific denominator: ```text line revenue cost margin_pct A 100.00 60.00 40.0 B 2.00 1.00 50.0 C 20000.00 19000.00 5.0 ``` SUM(margin_pct) = 95.0, which corresponds to nothing at all. AVG(margin_pct) = 31.7%, which is defensible arithmetic but almost never the business answer: it says line B, worth two dollars, carries the same weight as line C, worth twenty thousand. The true margin over these three lines is (20102 − 19061) / 20102 = 5.18%, dominated by line C exactly as the business intends. ## The modelling fix: store the components A fact table should hold measures that are **as additive as possible**. Concretely: - store `revenue_amount` and `cost_amount`, not `margin_pct` - store `impressions` and `clicks`, not `ctr` - store `order_amount` and `order_count`, not `avg_order_value` - store `on_time_shipments` and `total_shipments`, not `on_time_rate` Then the ratio is computed after aggregation: ```sql SELECT d.category_name, (SUM(f.revenue_amount) - SUM(f.cost_amount)) / NULLIF(SUM(f.revenue_amount), 0) AS margin_pct FROM fct_order_line f JOIN dim_product d ON d.product_key = f.product_key GROUP BY d.category_name; ``` This has three properties the stored column lacks. It is correct at **every** grain, because the division happens after the sums. It is **automatically weighted** by revenue, which is what "our margin" means. And it is **defined once**, so two dashboards cannot disagree about what margin is. Note the `NULLIF` guard: post-aggregation ratios divide by a sum, and a group whose denominator sums to zero must yield NULL rather than an error or a spurious value. ## Where the definition should live Deleting the bad column is necessary but not sufficient, because the next analyst will recreate it. Push the definition to where queries are written: 1. **Publish the calculation, not the number.** Define margin as a calculated measure in the semantic layer, or expose a view that computes it; consumers select a measure rather than a column. 2. **Do not expose a non-additive column as a plain numeric field** in a self-service model. If a tool lets a user drag it into a SUM, someone will. 3. **Name honestly if you must store it.** A rate stored at the atomic grain should be called something that signals its scope — `line_margin_pct` — so a summed total is obviously nonsense rather than plausibly a company figure. 4. **Test it.** A model test that compares the ratio computed at a fine grain and rolled up against the same ratio computed at the coarse grain catches the class of bug outright. ## When a ratio may be materialised Performance sometimes argues for precomputing. The rule is that you may materialise a ratio **only at the grain it will be consumed at**, and you must keep the components alongside it so any coarser rollup can be recomputed. A monthly-by-category table can carry `margin_pct` because nobody will sum months and categories out of it — provided `revenue_amount` and `cost_amount` are also there for the people who will. ## Other non-additive measures to recognise - **Unit price.** Summing prices across order lines is meaningless; the additive pair is amount and quantity. - **Distinct counts.** Distinct customers per region cannot be summed to distinct customers nationally, because customers overlap regions; it must be recomputed from atomic rows at each grouping. - **Precomputed averages.** An `avg_basket_size` column has the same weighting problem as any ratio. - **Percentages of a total.** These depend on the filter context, so they are not even stable when a user changes a slicer. ## Fixing an already-published model Because the wrong number has usually been on a dashboard for months, treat this as a correction with an audit trail: identify every consumer of the column, publish the corrected measure alongside it, quantify the difference on a few headline figures so finance is not surprised, then remove the old column. Silently changing the number under an unchanged name is how a data team loses credibility. ## Common mistakes Swapping SUM for AVG is the reflexive fix and is wrong for anything where row size varies — it answers "the average of our line margins", not "our margin". Weighting manually with `SUM(margin_pct * revenue) / SUM(revenue)` gets the right number but reintroduces the definition in every query. And leaving the stored ratio in place "because some report uses it" guarantees the bug returns.
- Why isn't AVG(margin_pct) an acceptable fix?Because it weights every fact row equally regardless of size. A two-dollar order line moves the average as much as a twenty-thousand-dollar one, so the figure drifts with the number of small transactions rather than with profitability. The business meaning of margin is revenue-weighted, which is exactly what dividing summed components gives you for free.
- Is a count of distinct customers additive, semi-additive or non-additive?Non-additive. Distinct counts cannot be summed across any dimension, because the same customer can appear in two regions, two products and two months, so adding regional distinct counts over-counts. It must be recomputed from the atomic fact rows at each grouping, which is why pre-aggregated tables cannot carry it as a summable column.
- When is it acceptable to materialise a ratio in a fact table at all?Only at the exact grain it will be consumed at, and only with the additive components stored beside it. A monthly-by-category summary can carry a margin percentage because nobody rolls it up further, but the revenue and cost columns must remain so any coarser or differently-sliced question can be recomputed correctly.
saying these in an interview costs you the question
- Replaces SUM with AVG and calls the percentage fixed
- Says the query is wrong rather than the model
- Keeps the stored ratio because a report already uses it
- Thinks a percentage is semi-additive rather than non-additive
- Weights the ratio by hand in each query instead of defining it once