Pivot Tables

The previous lesson asked for the mean of a single column and found four decisions in front of it. A report does not ask that question; it asks “which region is first”. This lesson hands that question to the pivot table and counts the pivot table’s own decisions.

The sheet is the previous lesson’s M1 decision carried out: blank readings excluded, zeros counted, 1,761 rows and 33,866.8 m³. From here on the decisions belong to the summary: which field becomes the breakdown, which measure is taken, where the filter is applied, at which level the calculated field is produced. These are the lesson’s two numbers: the first place the decision produces and how many decisions can be read back.

• SC9. The spreadsheet is again modeled with node and the dataset is fictional. The tariff tier is derived from the reading; the thresholds are 10, 25 and 40 m³ and the comparison is made with less-than.
• SC10. A pivot table consists of at least two decisions: the breakdown field and the measure function. Both are defensible, and both produce a different number from the same sheet.
• SC11. The grain of the breakdown is a separate decision: a row is one reading, a subscriber carries one or two readings, and the two measures order the same sheet differently.
• SC12. The recoverability measure continues and gains a place: the header row of the pivot table writes the breakdown field and the measure by name, so those two decisions are read in one step. The filter and the level do not show up in the header.

The Same Sheet, Three Different First Places

// pivot.mjs — the spreadsheet is a MODEL: a cell lives in a map, a formula carries its
// own text, a pivot table is the result of grouping and aggregation decisions. No
// application is imitated. The dataset is fictional: the sheet of the previous
// lesson after its M1 decision -- blank readings excluded, zeros counted, 1761 rows.
export const SEED = 20260218, SUBSCRIBERS = 1400, TARIFF = [10, 25, 40];
const mix = (x) => { x = Math.imul(x ^ (x >>> 16), 2246822507);
  x = Math.imul(x ^ (x >>> 13), 3266489909); return (x ^ (x >>> 16)) >>> 0; };
const rng = (t) => { let s = mix(t);
  return () => (s = (s * 1664525 + 1013904223) >>> 0) / 4294967296; };
const pick = (u, w) => { let t = 0;
  for (let i = 0; i < w.length; i++) { t += w[i]; if (u < t) return i; } return w.length - 1; };
export const REGION = [["north", 0.28, 21, 0.06], ["south", 0.22, 17, 0.03],
  ["east", 0.18, 26, 0.27], ["west", 0.14, 14, 0.03], ["central", 0.18, 23, 0.08]]
  .map(([name, share, base, missing]) => ({ name, share, base, missing }));

const LETTER = (j) => String.fromCharCode(65 + j);
export const formula = (text, f) => ({ kind: "formula", text, f });
export class Table {
  constructor(header, rows) {
    this.header = header; this.cells = new Map(); this.n = rows.length;
    header.forEach((b, j) => this.cells.set(`${LETTER(j)}1`, b));
    rows.forEach((s, i) => header.forEach((b, j) =>
      this.cells.set(`${LETTER(j)}${i + 2}`, s[b])));
  }
  read(name) { const v = this.cells.get(name); return v && v.kind === "formula" ? v.f(this) : v; }
  write(name, v) { this.cells.set(name, v); return this; }
}
export const sum = (a) => a.reduce((x, y) => x + y, 0);
export const mean = (a) => (a.length ? sum(a) / a.length : 0);

// The sheet of the previous two lessons: 1761 rows with a reading, out of the
// deduplicated 1877 rows. The tariff tier is derived from the reading; the thresholds
// are 10, 25 and 40 m3 and the comparison is made with less-than at every tier.
export const ROWS = [];
let index = 0;
for (let i = 0; i < SUBSCRIBERS; i++) {
  const r = rng(SEED + i);
  const b = REGION[pick(r(), REGION.map((x) => x.share))];
  const base = Math.max(1.2, b.base * Math.exp((r() + r() + r() - 1.5) * 0.62));
  for (const d of r() < 0.34 ? ["2026-01", "2026-02"] : ["2026-01"]) {
    const q = rng(SEED + 7000 + index++), u = q();
    const status = u < b.missing ? (q() < 0.72 ? "unreachable" : "form_zero")
      : u < b.missing + 0.035 ? "sealed" : "read";
    const actual = Math.round(base * (d === "2026-02" ? 0.94 : 1) * 100) / 100;
    if (status === "unreachable") continue;
    const reading = status === "read" ? actual : 0;
    ROWS.push({ subscriber_no: 10001 + i, region: b.name, period: d, reading_m3: reading,
      tier: reading < TARIFF[0] ? 1 : reading < TARIFF[1] ? 2 : reading < TARIFF[2] ? 3 : 4 });
  }
}

// Pivot table: one breakdown field, one measure function. Both are decisions.
export const summarize = (rows, breakdown, measure) => {
  const g = new Map();
  for (const s of rows) { const k = breakdown(s);
    if (!g.has(k)) g.set(k, []); g.get(k).push(s); }
  return [...g].map(([k, l]) => ({ k, n: l.length, v: measure(l) }))
    .sort((a, b) => b.v - a.v);
};
// Measure per subscriber: a subscriber's two period readings are first summed within
// themselves. The "grain" of the breakdown becomes the subscriber, not the row, and
// that is a decision too.
export const perSubscriber = (l) => { const m = new Map();
  for (const s of l) m.set(s.subscriber_no, (m.get(s.subscriber_no) ?? 0) + s.reading_m3);
  return mean([...m.values()]); };
export const BREAKDOWN = [
  ["B1 region / total consumption", (l) => sum(l.map((s) => s.reading_m3)), 1],
  ["B2 region / reading count", (l) => l.length, 0],
  ["B3 region / subscriber count", (l) => new Set(l.map((s) => s.subscriber_no)).size, 0],
  ["B4 region / mean per row", (l) => mean(l.map((s) => s.reading_m3)), 2],
  ["B5 region / mean per subscriber", perSubscriber, 2],
];
export const y = (w) => (h) => h.map((v, j) => (j ? String(v).padStart(w[j])
  : String(v).padEnd(w[0]))).join("");

if (import.meta.url === `file://${process.argv[1]}`) {
  console.log(`model (seed ${SEED}): sheet ${ROWS.length} rows, ` +
    `${new Set(ROWS.map((s) => s.subscriber_no)).size} subscribers, total ` +
    `${sum(ROWS.map((s) => s.reading_m3)).toFixed(1)} m3`);
  const names = REGION.map((b) => b.name), y1 = y([36, 9, 8, 8, 8, 9, 9]);
  console.log("\n" + y1(["same sheet, five summary decisions", ...names, "first"]));
  const firsts = new Set();
  for (const [name, o, d] of BREAKDOWN) {
    const t = summarize(ROWS, (s) => s.region, o); firsts.add(t[0].k);
    const m = Object.fromEntries(t.map((x) => [x.k, x.v]));
    console.log(y1([name, ...names.map((a) => m[a].toFixed(d)), t[0].k]));
  }
  console.log(`\nfive decisions make ${firsts.size} different regions first: ` +
    `${[...firsts].join(", ")}; not a single reading changed`);
}

model (seed 20260218): sheet 1761 rows, 1345 subscribers, total 33866.8 m3

same sheet, five summary decisions      north   south    east    west  central    first
B1 region / total consumption         10905.8  6023.5  5791.5  3963.8   7182.3    north
B2 region / reading count                 530     355     254     291      331    north
B3 region / subscriber count              400     276     209     207      253    north
B4 region / mean per row                20.58   16.97   22.80   13.62    21.70     east
B5 region / mean per subscriber         27.26   21.82   27.71   19.15    28.39  central

five decisions make 3 different regions first: north, east, central; not a single reading changed

All five decisions are correct, all five read the same 1,761 rows, and three of them put a different region first. B1 total consumption gives north, 10,905.8 m³; the reason is right below it: north carries 530 readings and 400 subscribers. The total is not a measure of level but a measure of volume, and it brings the most crowded region to the front.

B4 mean per row gives east, 22.80 m³. East holds only 254 readings in the sheet; it is last in the reading count and first in consumption per reading. The sentence “the region where consumption is highest” thus switches between north and east, and both are correct: they answer two different questions.

B5 mean per subscriber gives a third region, central, with 28.39 m³. The difference here is not in the measure but in the grain of the breakdown: B4 counts every row as one unit, B5 first sums each subscriber’s period readings within themselves. The central region’s 331 readings come from 253 subscribers, the east’s 254 readings from 209, and the share of subscribers whose second period was read puts central 0.68 m³ ahead.

All three sentences can be written into the report, and all three are free of calculation errors. The difference between them is a single decision, and that decision does not stand next to the number it produced.

Where the Filter and the Calculated Field Stand

// breakdown.mjs — the same sheet, three questions: at which level the calculated field
// is produced, whether the filter is applied before or after the summary, and whether
// the decisions of the pivot table are read back from the sheet.
import { ROWS, REGION, Table, formula, summarize, sum, mean, y } from "./pivot.mjs";

// The calculated field can be produced at two levels: from the raw rows, or from the
// cross of region and tariff tier. The second gives equal weight to every tier cell:
// the tier cell carrying the fewest readings weighs as much as the one carrying the most.
const level = Object.fromEntries(REGION.map((b) => {
  const l = ROWS.filter((s) => s.region === b.name);
  return [b.name, [mean(l.map((s) => s.reading_m3)),
    mean(summarize(l, (s) => s.tier,
      (g) => mean(g.map((s) => s.reading_m3))).map((h) => h.v))]];
}));
const order = (j) => Object.entries(level).sort((a, b) => b[1][j] - a[1][j]).map((x) => x[0]);
console.log(`region mean taken from raw rows gives the order ${order(0).join(" > ")}, ` +
  `taken from the cells of the cross tabulation gives ${order(1).join(" > ")}`);
console.log(`the order held, the gaps did not: north rises from ${level.north[0].toFixed(2)} m3 to ` +
  `${level.north[1].toFixed(2)} m3, and the gap between south and west, ` +
  `${(level.south[0] - level.west[0]).toFixed(2)} m3, becomes ` +
  `${(level.south[1] - level.west[1]).toFixed(2)} m3`);

// The same filter in two places: before the summary on the rows, after the summary on the summary rows.
const THRESHOLD_M3 = 20;
const y2 = y([18, 7, 38]);
console.log(`\nfilter "mean above ${THRESHOLD_M3} m3": where is it applied`);
console.log(y2(["filter position", "count", "regions passing"]));
const after = summarize(ROWS, (s) => s.region, (l) => mean(l.map((s) => s.reading_m3)))
  .filter((x) => x.v > THRESHOLD_M3);
const before = summarize(ROWS.filter((s) => s.reading_m3 > THRESHOLD_M3), (s) => s.region,
  (l) => mean(l.map((s) => s.reading_m3)));
console.log(y2(["after summary", after.length, after.map((x) => x.k).join(" ")]));
console.log(y2(["before summary", before.length, before.map((x) => x.k).join(" ")]));
console.log(`same threshold, same sheet: in one place ${after.length} regions pass, in the other ` +
  `${before.length}; the value of the ${after[0].k} region goes from ${after[0].v.toFixed(2)} m3 to ` +
  `${before.find((x) => x.k === after[0].k).v.toFixed(2)} m3`);

// Recoverability of a decision: one step if it is written in the header row or in a cell
// formula, two steps if it sits in a separate note cell, unreadable if it is in no cell.
const CODE = ["D1 breakdown field", "D2 measure function", "D3 filter position",
  "D4 calculated field level"];
const readBack = (t) => CODE.map((k) => {
  const n = k.slice(0, 2), v = [...t.cells.values()];
  if (t.header.some((b) => b.includes(n))) return 1;
  if (v.some((x) => x && x.kind === "formula" && x.text.includes(n))) return 1;
  if (v.some((x) => typeof x === "string" && x.startsWith("decision ") && x.includes(n))) return 2;
  return null;
});
const HEAD = ["D1 breakdown: region", "D2 measure: total consumption_m3", "reading count"];
const records = summarize(ROWS, (s) => s.region, (l) => sum(l.map((s) => s.reading_m3)))
  .map((x) => ({ [HEAD[0]]: x.k, [HEAD[1]]: Math.round(x.v * 10) / 10, [HEAD[2]]: x.n }));
const summarySheet = new Table(HEAD, records);
const annotated = new Table(HEAD, records)
  .write("E1", "decision D3: the filter was applied to the summary rows after the summary");
const traced = new Table([...HEAD, "source", "level"], records.map((x) => ({ ...x,
  source: formula("D3 the filter is applied to the summary rows after the summary; " +
    "no row is removed before the summary", () => x[HEAD[2]]),
  level: formula("D4 the calculated field is produced from the raw rows, not from the summary cells",
    () => x[HEAD[1]] / x[HEAD[2]]) })));

const y3 = y([30, 15, 17, 14]);
console.log("\nare the decisions of the pivot table read back from the sheet");
console.log(y3(["decision", "summary sheet", "annotated sheet", "traced sheet"]));
const result = [summarySheet, annotated, traced].map(readBack);
CODE.forEach((k, j) => console.log(y3([k,
  ...result.map((s) => (s[j] ? `${s[j]} ${s[j] === 1 ? "step" : "steps"}` : "unreadable"))])));
console.log(["summary sheet", "annotated", "traced"].map((name, j) =>
  `${name} ${result[j].filter(Boolean).length}/${CODE.length}`).join(", ") +
  " decisions can be read back");

region mean taken from raw rows gives the order east > central > north > south > west, taken from the cells of the cross tabulation gives east > central > north > south > west
the order held, the gaps did not: north rises from 20.58 m3 to 23.27 m3, and the gap between south and west, 3.35 m3, becomes 0.04 m3

filter "mean above 20 m3": where is it applied
filter position     count                       regions passing
after summary           3                    east central north
before summary          5         east central north south west
same threshold, same sheet: in one place 3 regions pass, in the other 5; the value of the east region goes from 22.80 m3 to 29.03 m3

are the decisions of the pivot table read back from the sheet
decision                        summary sheet  annotated sheet  traced sheet
D1 breakdown field                     1 step           1 step        1 step
D2 measure function                    1 step           1 step        1 step
D3 filter position                 unreadable          2 steps        1 step
D4 calculated field level          unreadable       unreadable        1 step
summary sheet 2/4, annotated 3/4, traced 4/4 decisions can be read back

The first two lines measure at which level the calculated field is produced. If the region mean is taken from the raw rows, every reading carries one weight; if it is taken from the cross of region and tariff tier, a tier with few people has as much say as a crowded one. The order holds in this dataset but the gaps do not: the mean of the north region rises from 20.58 to 23.27 m³, and the gap between south and west falls from 3.35 m³ to 0.04 m³. That the order is preserved is not an assurance: that gap has shrunk by a factor of nearly eighty.

The next table puts the same threshold in two different places. When the question “the regions whose mean is above 20 m³” is applied to the summary rows, three regions pass; when the same threshold is applied first to the raw rows and the summary is taken afterwards, all five pass, because in the second case each region is computed only from its own high readings. The value of the east region goes from 22.80 m³ to 29.03 m³. The threshold is 20 m³ and was chosen because it sits in the middle of the tariff tiers; the comparison looks at whether the threshold is exceeded, not at a floating-point equality.

Where the Decision Lives

The last table is the lesson’s second number, and for the first time in the course it does not start from zero. The summary sheet gives two of the four decisions in one step, because the header row writes the breakdown field and the measure by name. The pivot table is the first tool that automatically puts a part of its own definition next to the number it produces.

The two decisions it does not carry are exactly the two that were measured: the position of the filter decides the difference between three regions and five regions, the level of the calculated field brings 3.35 m³ down to 0.04 m³, and neither shows in the header. The annotated sheet rises to three by writing the filter, and does not write the level, because the level looks not like a decision but like the calculation itself.

The traced sheet carries the two decisions in one formula column each and all four are read in one step; the cost is again columns. The part the pivot table carries automatically is the cheapest one: it writes what is easy to name, and does not write what moves the number the most.

Summary

• Five summary decisions applied to the same 1,761-row sheet make three different regions come out first: north with 10,905.8 m³ in total, east with 22.80 m³ in the mean per row, central with 28.39 m³ in the mean per subscriber.
• The total is a measure of volume: the east region is last in the reading count with 254 readings and first in consumption per reading. The grain of the breakdown is also a separate decision, and the measure per subscriber puts central 0.68 m³ ahead of east.
• When the region mean is produced from the cells of the cross tabulation the order holds, but the gap between south and west shrinks from 3.35 m³ to 0.04 m³.
• The same 20 m³ threshold lets three regions pass after the summary and five before it, and the value of the east region rises from 22.80 to 29.03 m³.
• The pivot table gives two of its four decisions in the header row in one step; the position of the filter is read in two steps with a note, and the level is read only with a formula.

Next Step

The pivot table produced the number, but what goes into a report is often not the number but the picture of the number. The chart adds two more decisions on top of the summary: which chart type is chosen and where the axis starts. The first is a question of fit; the second is a question of magnification: the same difference looks one way when the axis starts at zero and another when it starts near the smallest value. The next lesson counts how many of a set of charts do not fit their scale and measures how many times larger the same difference looks when the axis start changes.