Day 3 — Calculated fields - row by row

A calculation that runs once per row, before anything is added up.

Two kinds of calculation, and Tableau will not mix them

A row-level calculation runs on every row of your data. An aggregate calculation runs on a group of rows. Tableau keeps them strictly apart, and the error it gives when you mix them is the most common one beginners hit.

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.

Row-level: computed for every single row

Each row gets its own answer, exactly as if you had added a column to the spreadsheet. Notice Margin % is the same for every laptop row - it is a property of the product, not the order size.

The same field, aggregated

All three agree. SUM([Sales] - [Cost]) subtracts on each row then adds up; SUM([Sales]) - SUM([Cost]) adds up then subtracts. For addition and subtraction the order does not matter.

For division it matters enormously

These answer different questions The first is total revenue divided by total units - the true average price per unit. The second averages each order’s unit price, giving every order equal weight regardless of size, so a single-unit order counts as much as a thirty-unit one.

Neither is a bug. But if someone asks for "average selling price" and you hand over the second, the number is wrong for the question. Whenever a calculation divides, decide whether you are dividing totals or averaging ratios.

The error everyone meets

Tableau refuses. Once any part of a calculation is aggregated, every part must be. The fix is to aggregate the other field too:

Why Tableau is strict about this SUM([Sales]) + [Quantity] has no meaning: the left side is one number for the whole group, the right side is 48 different numbers. There is no sensible answer, so rather than guess, Tableau stops.

Building a calculation up in steps

In real Tableau you would define Profit once as its own field and then write [Profit] / [Sales], referring to it by name. Small named calculations stacked on each other are far easier to debug than one long formula.

Try these yourself

Write a row-level calculation for profit per unit.
Preview it over the first six rows.
Write the aggregate version of total profit two different ways.
Explain why SUM([Sales]) * [Quantity] is rejected.
Work out the average price per unit for the whole data source.

← PreviousDay 2 — How Tableau aggregates Next →Day 4 — IF, CASE and conditional logic