Measures that add up (and those that don't)
🎯You'll be able to
Tell additive, semi-additive and non-additive measures apart, and store and calculate each so totals stay correct.
🏠The everyday picture
You can add up a week's sales to get the week's total. You can't add up each day's closing bank balance to get the week's balance, or add the day's average prices to get anything at all.
📘What it really is
- Additive: can be summed across every dimension: quantity, amount, net amount.
-
Semi-additive: can be summed across some dimensions but not time: stock on hand, account balances. Across time, take the last value or the average.
-
Non-additive: ratios and averages: average order value, conversion rate, margin percentage. Don't sum them; and averaging them gives the wrong answer too when the groups differ in size.
-
Store the parts, calculate the ratio last: keep net revenue and delivered orders, then divide for the group being shown. SabziSathi's
mart_daily_salesstoresnet_revenue_paiseanddelivered_ordersfor exactly this reason. -
Distinct counts are non-additive: customers per area don't add up to customers overall when people move or appear in several groups.
- Name measures by what they include:
net_amount_paise(delivered only) versusamount_paise(as charged).
📝A worked example
Input: two ways to get October's average order value from mart_daily_sales:
select avg(avg_order_value_paise) and select sum(net_revenue_paise) / sum(delivered_orders). Then compare customers:
select count(distinct customer_key) from marts.fct_order_lines where status = 'delivered' with
select sum(active_customers) from marts.mart_area_customers.
Expected output: the average of daily store averages is ₹672.98; the true figure is ₹673.67. Distinct customers are 598, but the area counts add up to 630, because customers who moved during the month are counted in both areas. Small gaps here; at real scale, such gaps reach board reports.
🌍Where it's used
- Retail: average basket size across stores of very different size.
- Banking: month-end balances, which are semi-additive.
- Marketing: conversion rates by channel that must not be averaged.
📝Case scenario
A chain of coaching centres reported its average pass rate as the average of each centre's rate: 82%. Two tiny centres had 100% with a handful of students each, while the largest centre had 68%. Students passed divided by students sitting gave 74%.
🖱️Try it
Compute net revenue per delivered order for each store from mart_daily_sales, the right way. Then average the three results and compare
with the month's overall figure. Success: you can explain why the average of the three isn't ₹673.67.
⚠️Common mistakes
- Summing ratios. Sign: a "total" conversion rate over 100%. Fix: store numerators and denominators.
- Averaging averages. Sign: small groups pull the total. Fix: divide the summed parts.
- Adding distinct counts across groups. Sign: more customers than exist. Fix: count distinct at the level asked.
🛠️For your project
Project 3 test 2 checks the dashboard's average order value is ₹673.67 (67,367 paise), computed from the summed parts.
✅Quick check
Is average order value additive?
Show the answer
No: store revenue and orders, and divide for the group shown.
Why don't customers per area add up to all customers?
Show the answer
Some customers appear in more than one area.