Query a small star schema. Units and revenue add up in every direction. Average unit price is a ratio and distinct orders is a count, and neither can be relied on to add up. Change the measure and see which totals survive.
Declared grainone row of factSalesLine is one product line on one customer order. It loads with 17 such rows across 9 orders, 4 products, 4 stores and 4 months.
Average unit price, whole gridA$17.44
Average unit price, product category by store region, nothing filtered out
Product category
NSW
VIC
QLD
Total
Coffee
A$17.46
A$14.62
A$13.33
A$17.22
Homewares
A$25.00
A$25.00
A$25.00
A$25.00
Tea
A$18.00
A$18.00
—
A$18.00
Total
A$17.63
A$16.14
A$14.06
A$17.44
An em dash means no fact row exists for that combination. It is not a zero. This grid has 1 of 9 combinations with no rows.
Sum then divide A$17.44Average of averages A$19.55Too high by A$2.11
Dividing summed revenue by summed units gives A$17.44. Averaging the 8 cell averages instead gives A$19.55, which is A$2.11 too high. The reason is weighting. Averaging the averages hands each of the 8 cells 12.5% of the answer, while the Coffee by NSW cell holds 88.7% of the units and the Homewares by QLD cell holds 0.2%. Run the same fault over a region holding one enormous store and nine small ones and the nine small ones carry nine tenths of the weight. Store revenue and units as two additive measures and divide the sums at query time, and the figure is right at every level with no special handling.
Thirty daily closing balances, averaging A$201,237, add to A$6,037,104, roughly A$6 million of cash the business never held. Cash is a stock measured at a point in time, so the month is reported as the closing balance A$213,015, or as the average daily balance when the question is how much cash was typically on hand.
The schema is a stylised toy, small enough to check by hand, not a real company. Every cell, subtotal and total above is recomputed from the fact rows whenever a control changes. Money is held in integer cents and rounded only where it is shown. The daily cash balances come from a fixed seeded generator, so they are the same every time this loads.