Day 2 — How Tableau aggregates
Tableau never shows you a raw number. It always aggregates - and knowing which aggregation is running is half the battle.
Every measure arrives with an aggregation attached
Drag Sales into a view and Tableau does not show 48 numbers. It shows one, because it wrapped the field in SUM without asking.
Every calculation on this page runs against the Orders data source below - 48 rows, dimensions in blue, measures in green, exactly as Tableau colours them.
Same column, five different questions. The average order is far above the median, which tells you a handful of large laptop orders are pulling it up.
COUNT and COUNTD are different
The aggregation changes with the view
This is the idea that makes Tableau feel magical and occasionally maddening. The calculation never changes; the set of rows it runs over does.
North sells the most overall. Look at the average order column before you decide North is the best-performing region.
The Grand Total is recalculated, not added up
Sales adds up because addition works that way. A ratio does not. This is correct, and it is the first sign that a total row is its own calculation rather than a summary of the rows above it.
Aggregating a condition
You can put an IF inside an aggregation. Tableau evaluates the IF on every row, then adds up the results.
Leaving the ELSE off gives Null for non-matching rows, and SUM ignores Nulls - so both forms give the same answer here. Day 6 explains when they differ.
Averages of averages
Two different questions. The first is the average order value. The second is revenue per unit sold. Neither is wrong - but handing over the wrong one answers a question nobody asked.
Try these yourself
- Find the largest single order value.
- Count how many distinct ship modes are used.
- Work out the average profit per order, rounded.
- Break Sales and Profit Ratio down by Manager.
- Explain in one sentence why a Profit Ratio grand total is not the sum of the rows.