A report averages the amount column of a table holding one row per measurement across six measures and returns a meaningless number — why?

## Why the number is meaningless In a table with one row per measurement, a cell's meaning is supplied by its row's name column, not by the column it sits in. The value column is therefore a union: footfall in people, revenue in currency, returns in items, a temperature in degrees. A mean over that union adds quantities that have no common unit and divides by a row count that depends on which measures happened to arrive. Nothing about it is salvageable by rounding or by a larger sample. It is not an imprecise number, it is a number with no unit. The reason it went unnoticed is that the computation is perfectly well formed. The column exists, its cells are numbers, an aggregate over numbers returns a number. There is no failure to catch, which is exactly why this shows up in a finished report rather than in a stack trace. ## The same fault in other clothes - **A maximum or a minimum** over the value column returns whichever measure is numerically largest or smallest, which is a fact about units rather than about the business. - **A total** is a sum of unlike things and behaves like one: it moves whenever the mix of measures changes, even if nothing measured changed. - **The table's row count**, read as a count of subjects, is wrong by roughly the number of measures per subject. It counts measurements. - **A per-subject mean of the value column** has the unit problem *and* a weighting problem: subjects with more measurements pull the answer, so the result is weighted by measurement count rather than by subject. - **A distribution or a spread** computed over the union describes the mixture of measures, not the variability of any one of them. ## Where the trap comes from With one column per measure, the semantics of a number are carried by the header. An aggregate names a column, and in naming the column it has already named the quantity; a column holding two different units cannot arise by accident. Naming a measurement in a cell moves that semantics one column sideways, so naming the value column names nothing at all. Every aggregate silently acquires a precondition — *which rows?* — that the code has no way to make you state. That is the property to articulate in an interview: this arrangement trades a schema that cannot change for a semantics that is not enforced anywhere. ## Conditioning on the name column The repair is mechanical, and the discipline around it is the interesting part: 1. **Select the rows for one measure, then aggregate, and repeat per measure.** Every figure in the report is then a figure about a named quantity. 2. **Match the name to what the producer actually emits.** A measure that was renamed, or split into two names, still yields rows and still yields a plausible number — just over fewer rows than you think. Nothing in the layout announces that a name has gone. 3. **Treat an empty selection as a stop condition, not a footnote.** What an aggregate over no rows gives you **varies by design** — an absent-value marker, an error, or zero for a total — so do not rely on a particular one of those to alert you. Check the row count you selected on, and decide what the report does when it is zero. 4. **Read the row count of a filtered selection as a count of measurements of that one measure**, which is the only reading of it that is defensible. ## The rule worth having as a team An aggregate over a value column with no condition on the name column is a defect, and it is cheap to spot in review because it is visible in the expression itself — there is no need to know the data to see that a quantity was never named. Two habits follow, and both are judgment calls a senior person owns rather than rules a tool can impose: - Every number that reaches a reader should be traceable to a measure name that was stated somewhere in producing it. - Any code path that treats the value column as one quantity should be rewritten even when its current output happens to look sensible, because it looks sensible only for as long as the mix of measures stays as it is today — and the mix is data, so it will not. ## The short answer The number is meaningless because the arrangement moved a measurement's identity out of the header and into a cell, and the aggregate never read that cell. The general form of the lesson: in this layout, no read of the value column is complete until it has said which measure it is about.