Why Is My Average Wrong? Average of Averages versus Weighted Average
Resolve a common dashboard doubt with group sizes, a tested SQL example and a reconciliation exercise.
The doubt: can I average two branch averages?
Only when the intended weighting makes that valid. For an overall transaction average, groups with different transaction counts cannot normally receive equal weight. Define what one observation means before selecting a formula. A branch-level comparison and a transaction-level average answer different questions.
Imagine one practice group has two transactions of 100 and another has eight transactions of 20. The two group averages are 100 and 20. Their unweighted average is 60, while total value divided by transaction count is 360/10 = 36. These invented groups are arithmetic examples, not actual branches or company revenue.
A reproducible SQL example
The PostgreSQL query below uses group totals and counts. It was checked locally with PGlite, a PostgreSQL runtime.
WITH groups(name, total_value, transaction_count) AS (
VALUES ('A',200,2),('B',160,8)
)
SELECT SUM(total_value)::numeric / SUM(transaction_count) AS overall_average,
AVG(total_value::numeric / transaction_count) AS average_of_group_averages
FROM groups;Expected values are 36 and 60. Decimal conversion makes the division intent explicit. Do not round each group's average before combining it if the source totals are available: early rounding can introduce additional error.
Why does the dashboard disagree with my spreadsheet?
Check whether both use the same filters, time period, currency and observation unit. Then inspect the denominator. A spreadsheet may average visible group percentages while a report recalculates a ratio from totals. Both can produce valid numbers for different questions; the label must explain the measure.
If one dataset contains order lines and the other contains orders, reconcile that difference before blaming the calculation engine. Also check whether missing values are excluded or replaced by zero. A missing amount is not automatically a real zero-value transaction.
What should happen when there are no observations?
An average with a zero denominator is undefined. Decide how the report communicates that condition: a blank with a note may be more informative than a fabricated zero. Avoid silently converting every unavailable KPI to zero merely to fill a chart.
Practice and expected result
Add group C with total 120 from three transactions. The combined transaction average is 480/13, approximately 36.923. The unweighted group average becomes (100+20+40)/3, approximately 53.333. Explain which answer belongs under overall transaction average and which answers an equal-weight group question. Present the numerator and denominator alongside your answer so another learner can verify it.
Data Analytics course.
PostgreSQL aggregate reference.
Prepared by AI Leaf Global Learn for classroom learners in Mudichur/Tambaram, Chennai, and online learners across India. Confirm current course and batch details. Area tags describe audiences, not additional offices.