"Of Ontario's sales" vs "of online sales"

◈ 9 cards

The same cell, 930, is 46.6 % of Ontario's row, 30.4 % of the Online column and 15.1 % of the grand total. The question's "of …" names the base; prop.table(t, 1) is rows, prop.table(t, 2) is columns.

One cell, three percentages

Build the two-way pivot: prov in Rows, channel in Columns, Sum of amount in Values. With grand totals on, it reads:

prov    Online   Store   Grand Total
AB        1320     980          2300
BC         720     640          1360
ON         930    1065          1995
QC          90     395           485
Total     3060    3080          6140

The ON × Online cell is 930. As a share, that number has three honest meanings, and the question decides which one — by the words after of.

Worked example — three questions, one cell

Of Ontario's sales, what share came online? The base is Ontario's row: 930 / 1995 = 46.6 %. In Excel that is Show Values As → % of Row Total; in Sheets, the Values card's Show as → % of row. Every row then sums to 100 % across.

Of online sales, what share came from Ontario? The base is the Online column: 930 / 3060 = 30.4 %. Show Values As → % of Column Total; every column sums to 100 % down.

What share of all sales were Ontario online orders? The base is the grand total: 930 / 6140 = 15.1 %. Show Values As → % of Grand Total; the whole table sums to 100 %.

The two mirror questions are the trap, because each contains both words — Ontario and online. Find the noun after of: of Ontario's → the province is the base → the province is in Rows → row total. Of online → the channel is the base → the channel is in Columns → column total. Show Values As changes only the display; the field is still Sum of amount underneath, and switching it back to No Calculation restores the dollars.

The R side: xtabs, prop.table, addmargins

xtabs() builds the grid from the same formula aggregate() used, and prop.table() divides it by a margin: 1 for rows, 2 for columns, none for the grand total:

> t2 <- xtabs(amount ~ prov + channel, data = orders)
> round(prop.table(t2, 1) * 100, 1)
    channel
prov Online Store
  AB   57.4  42.6
  BC   52.9  47.1
  ON   46.6  53.4
  QC   18.6  81.4

The ON row is 46.6 + 53.4 = 100 — % of Row Total. prop.table(t2, 2) gives 30.4 for ON × Online, and prop.table(t2) 15.1. The grand totals the pivot shows for free are addmargins() in R:

> addmargins(t2)
     channel
prov  Online Store  Sum
  AB    1320   980 2300
  BC     720   640 1360
  ON     930  1065 1995
  QC      90   395  485
  Sum   3060  3080 6140

Two more twins. A pivot of two categoricals with Count in Values — how many orders per province and channel — is table(orders $p r o v, or d er s$ channel); addmargins() on it adds the 2 · 3 · 4 · 3 and 6 · 6 · 12 totals. And the row-percentage rule is the same for counts as for sums.

Type both, then fill the two-cell worksheet

Type the row-percentage and the margins calls. The worksheet asks for the row and column percentages of the ON × Online cell; the code block computes both from the row and column sums.

CRISP-DM: percentages of a total are data understanding → explore data, and choosing the right base is the first evaluation skill — a share with the wrong base answers a question nobody asked.

Sum of amount, prov × channel. The highlighted 930 is 46.6 % of its row (1995), 30.4 % of its column (3060) and 15.1 % of the grand total (6140).