Calculated Fields and Measures
Meera's new dashboard shows March gross margin as 37.5%. Dev's finance report says 40%. Same data, same month, and both built from the same star schema. The difference is not in the data. It is in where the division happened: one of them averaged percentages that should never be averaged.
The rule
Rows get calculated fields; totals get measures; ratios divide sums.
- A calculated field computes a value for each row:
line_revenue = qty × price, oris_late = delivered_date > promised_date. It is worked out row by row, before any grouping. - A measure is an aggregation that is recomputed for whatever rows are in view: all of March, one category, one warehouse. Total revenue is
SUM(revenue). Gross margin % is(SUM(revenue) − SUM(cost)) ÷ SUM(revenue).
-- Calculated field: one value per row
SELECT order_id, qty * price AS line_revenue
FROM order_lines;
-- Measure: recomputed for each group in view
SELECT p.category,
SUM(s.revenue) AS revenue,
100.0 * (SUM(s.revenue) - SUM(s.cost)) / SUM(s.revenue) AS gross_margin_pct
FROM fct_sales s
JOIN dim_product p ON p.product_key = s.product_key
GROUP BY p.category;Whether a number can be added up decides how its measure must be built:
- Additive: revenue, units, cost. Sum them across any dimension.
- Semi-additive: stock on hand, headcount, bank balance. Sum them across warehouses, but not across time: the first quarter's stock is the stock at the end of March, not the three month-ends added together.
- Non-additive: percentages, averages, distinct counts. Never sum them or take a plain average of them; rebuild them from their parts.
In March, bedsheets brought ₹8 lakh of revenue at a cost of ₹4.4 lakh, a 45% margin. Curtains and cushion covers brought ₹4 lakh at a cost of ₹2.8 lakh, a 30% margin. Meera's card averaged the two percentages. Dev divided the sums.
The shortcut
Before you SUM or AVERAGE a column, ask: does adding this across rows mean anything? If not, keep its parts and divide at the end.
Find what the card does
It averages the two category margins: (45 + 30) ÷ 2 = 37.5%.
Drills
Answer fast — trust the rule.
Which of these is semi-additive?
line_revenue = qty × price, computed for every order line, is a…
Weftwise had 20 warehouse staff at the end of January, February and March. What is the headcount for the first quarter?
Remember
- Rows get calculated fields; totals get measures; ratios divide sums.
- Ask "does adding this mean anything?" If not, keep the parts and divide at the end.
- Trap: averaging percentages, and summing stock or headcount over time.