CSV to Pivot

The totals row is where pivots go wrong

Here is the whole argument for this page in one sentence: a total is a statement about records, not a statement about the cells printed above it. Almost every pivot implementation I have opened treats it as the second thing, because the second thing is fast and, for a sum, indistinguishable from the first. Sum four cells or sum the underlying records and you land on the same number. So the shortcut passes its only obvious test and then ships.

Switch the aggregate to Average and the shortcut breaks quietly. Suppose a region sold through three channels: 90 orders averaging $120, 3 orders averaging $4,000, and 7 orders averaging $310. The true average across that region's 100 records is roughly $350. Average the three cell averages instead and you get about $1,477, a number four times larger that belongs to no order, no channel and no region. Nothing on screen flags it. The row still looks like a row, the total still looks like a total, and somebody quotes $1,477 in a meeting.

Distinct counts fail in the opposite direction and just as silently. If a customer bought through both Direct and Retail, they are correctly counted once in each of those two cells. Add the cells and that customer becomes two customers. Do it across four channels and a base of 400 real customers reports as 700. Sums inflate distinct counts; averages of averages distort in whichever direction the record counts are lopsided; medians of medians are not even approximately meaningful, since the median of a set is not recoverable from the medians of its parts under any weighting at all.

So this pivot does the slower thing. Every margin cell gets its own pass over the source table. A row total collects every record whose row field matches that label and aggregates them from scratch. A column total does the same down its column. The grand total aggregates the whole file. The average of a row is therefore the average of that row's records, and a distinct count of a row is the count of values distinct across that row's records rather than the sum of its cells. On a sum you will never notice the difference. On everything else, the difference is the reason the table is worth trusting.

Worked example: revenue by region and channel

Seven columns, 180 orders spanning 2024. The opening rows:

date,region,product,channel,units,unit_price,revenue
2024-01-02,West,Doohickey,Direct,58,211.64,12275.12
2024-01-04,West,Sprocket,Partner,13,40.73,529.49
2024-01-06,North,Sprocket,Retail,31,147.97,4587.07
2024-01-08,East,Gizmo,Direct,7,172.02,1204.15

Set Rows to region, Columns to channel, Values to revenue, leave Aggregate on Sum and Totals on. The panel shows a table titled revenue by region and channel with a kicker reading PIVOT and a caption reading Sum of revenue · 4 rows x 3 columns · totals recomputed from source rows. The header row reads region, then Direct, Partner, Retail in natural order, then Total. Four body rows follow, sorted the same way, and a Total row closes the table. Every figure is formatted as money, because revenue was profiled as money rather than as a plain number.

Now the interesting move. Switch Aggregate to Average. Every cell becomes the mean order value for that region and channel, and every total becomes the mean order value across that region's own orders. Take a pen to it: add the three cells in the West row, divide by three, and compare against the Total cell at the end of that row. They will not match, and the Total is the one that is right, because the three channels do not carry equal numbers of orders. That mismatch is not a rendering artifact, it is the arithmetic that most pivot tables get wrong without saying anything.

Switch Values to product and Aggregate to Count distinct and the same shape now counts how many distinct products each region and channel pairing sold. Add the West row's cells across and you will usually get a larger number than the West Total, because a product sold through two channels is one distinct product for the region and two cells' worth of counting. Again, the smaller recomputed figure is the true one.

Two notes may appear under the table depending on the file. If any row has an empty region, those records group under (blank) and a note says so rather than letting them vanish out of the grand total. If any revenue cell holds something unreadable, a note reports how many values could not be read as a number and were left out; they are excluded from the aggregation and never counted as zero, which would silently pull every average down.

What this page will not do

The honest edges, so you meet them here rather than halfway through a report:

• One field per axis. No nested row groups and no stacked column headers. If you need region inside country inside continent, build the composite key first in the app.
• One measure at a time. A single Values field with a single Aggregate. Sum of revenue and average of units side by side means two passes and two exports.
• A column cap of 60. A column field with more than 60 distinct values is refused, with the count stated. Use it as the row field instead.
• No calculated fields. There is no formula box, so margin percent or revenue per unit has to exist as a column in the file before it arrives here.
• No percent-of-total mode. Cells hold the aggregate, never a share of the row or column. Divide in the export.
• No filtering and no drill-down. Every row in the file is included, and clicking a cell does not open the records behind it. Both live in the app.
• A header row is required. Without one, the first data line becomes the field names and both dropdowns will offer nonsense that looks plausible.

Comma, semicolon, tab and pipe delimited files are all read without configuration, so a block of cells copied out of a spreadsheet works in the paste box.