Why is my total 12?

The pivot that says 12

A colleague rebuilds Lesson 7.1's pivot on a copy of orders where one amount — order 1003's 760 — was pasted from an email and arrived as text: it sits left-aligned in its cell. The Values zone now reads Count of amount and every province shows its number of orders: AB 2, BC 3, ON 4, QC 3, Grand Total 12. Nothing else changed.

Worked example — diagnose, fix, refresh, choose

Diagnose. Excel chooses the default summary function by inspecting the whole column: all numbers → Sum; any text or blank cell → Count. A Count where you expected a Sum is therefore not a pivot setting gone wrong; it is a report that the source column is not clean. (Module 2's alignment test finds the cell: the text 760 hugs the left edge.)

Fix the source, not the pivot. The tempting move is to open the field's settings and switch Count to Sum. It works — and it hides the problem: the text cell contributes nothing to a Sum in either tool, so ON reads 1235 instead of 1995 and the grand total 5380 instead of 6140, with no warning. Retype the cell as a number first.

Refresh. An Excel pivot is a snapshot of the source at the moment it was built or last refreshed. After the cell is fixed the pivot still says Count until you refresh it (PivotTable Analyze → Refresh). A Sheets pivot recalculates live, so the fix shows at once. Either way, once the column is all numbers the pivot returns to Sum of amount: ON 1995, Grand Total 6140.

Choose the function. The function is a property of the Values field. In Excel, open the field's Value Field Settings and pick from Summarize Values By: Sum, Count, Average, Max, Min (and more). In Sheets, the Values card has a Summarize by list. Choose Average and the pivot reads AB 1150, BC 453.33, ON 498.75, QC 161.67 — aggregate(amount ~ prov, data = orders, FUN = mean).

The five functions that matter, with their R names: Sum (sum), Count (length), Average (mean), Min (min), Max (max). Count counts rows, including a row whose amount is blank; Sum, Average, Min and Max ignore blanks — the same rule as the worksheet functions.

Order of operations, for the quiz

When a pivot shows Count of a numeric field: (1) find and fix the text or blank in the source, (2) refresh, (3) only then set the function you want. Doing (3) first produces a number that looks right and is not.

Read the fixed pivot

The numeric item asks for Sum of amount for ON after the fix. The questions cover the default, the fix order, the setting and the refresh.

CRISP-DM: the Count default is a data understanding → verify data quality signal surfacing inside an explore data tool.