Our dataset said the average loan was 2.3 million kroner. The number that actually mattered was 255,000. Both were correct. Only one of them was true.
This is a writeup of three ways a dashboard can be arithmetically perfect and still lie, using real figures from an analysis of 1,000 Norwegian debt consolidation applications. If you build reporting for anyone, you have probably shipped at least one of these.
1. Summing a field that contains two different things
A debt consolidation loan pays off your expensive credit card debt. It also, if you own property, rolls your existing mortgage into the same new loan. Same column in the database. Same loan_amount. Utterly different meaning.
SELECT AVG(loan_amount) FROM applications;
-- 2,300,000
That query is right and the answer is useless. Of that 2.3 million, roughly 1.9 million is an existing mortgage being moved from one lender to another. The expensive debt, the part the customer actually has a problem with, averages 255,000.
So the headline figure overstates the thing you care about by a factor of nine.
Nothing in the schema warns you. loan_amount is a number, AVG is a function, the result renders fine. The bug is that one column is holding two concepts and only a human who understands the domain will notice.
-- what you actually wanted
SELECT AVG(unsecured_debt) FROM applications;
-- 255,000
If a column can mean two things depending on another column, split it. Every time.
2. Reporting the mean when the distribution has a tail
Income in this dataset runs from ordinary salaries up to about five million kroner. A handful of very high earners drag the mean upward:
- Mean income: ~635,000
- Median income: 647,000 for homeowners, 550,000 for renters
Look at what happens there. The mean sits between the two medians and describes neither group. Someone reading only the mean concludes the typical applicant earns 635,000. Nobody earns 635,000. It is an artefact.
df.groupby('housing')['income'].agg(['mean', 'median', 'count'])
Cheap rule that has never failed me: if mean and median differ by more than a few percent, the mean is not describing anybody and should not be the number on the card. Show the median, and show the spread.
3. Averaging across segments that behave differently
This is the one that cost us the most rework.
Applicants split into two groups with genuinely different outcomes:
| Rate before | Rate after | Change | |
|---|---|---|---|
| Owns property (300 applicants) | 12.5% | 7.6% | −4.9pp |
| No property (679 applicants) | 15.2% | 12.6% | −2.6pp |
A single blended "average rate reduction" across all applicants produces a number that is true of the population and false of every individual in it. Worse, it is actively misleading in opposite directions for the two groups: it undersells the result for one and oversells it for the other.
The fix is not statistical, it is editorial. Decide what question the reader is asking. They are asking what happens to someone like me. That question has two answers here, so the chart needs two rows and no total.
I have started treating a total row as something you justify rather than something you add by default.
The thing that ties all three together
Each of these is a case where the aggregation is correct and the framing is wrong. Your tests will not catch them, because nothing is broken. AVG returns the average. The column contains what it contains.
The only defence I have found is to ask, for every number on a page: if a reader acted on this alone, what would they get wrong? For the 2.3 million figure, they would conclude Norwegian households carry nine times more expensive debt than they do. That question takes ten seconds and it has caught more real problems for me than any amount of test coverage.
The figures above come from an analysis of 1,000 debt consolidation applications published by samlegjeld.no. The full breakdown is there.
Top comments (0)