In Tableau, what is the difference between a row-level and an aggregate calculated field?
answer
- one runs per data row, one per mark
- the shelves decide how many rows survive
- cannot mix a SUM with a raw column
- ratio of sums, not average of ratios
basics
~20 sA row-level calculation evaluates once per underlying data row and can be used as a dimension. An aggregate calculation such as SUM([Profit])/SUM([Sales]) evaluates once per mark, after Tableau groups rows to the view's level of detail.
solid answer
~40 sA **row-level** calculation like `[Sales] - [Cost]` or `DATEDIFF('day', [Order Date], [Ship Date])` contains no aggregation, so Tableau evaluates it for every row of the data source — usually by pushing it into the query it sends — and the result behaves like any other field, including as a dimension. An **aggregate** calculation like `SUM([Profit]) / SUM([Sales])` evaluates once per mark, after rows are grouped to the dimensions in the view, so its value changes when you change the shelves. You cannot mix the two in one expression: `SUM([Sales]) - [Cost]` fails with "cannot mix aggregate and non-aggregate arguments", because one side has a value per mark and the other a value per row. Aggregate both sides, or aggregate the whole row-level expression.
code
text · 8 lines// Row-level: one value per row of the data source
[Sales] - [Cost]
// Aggregate: one value per mark in the view
SUM([Sales]) - SUM([Cost])
// Illegal: one term is per mark, the other per row
SUM([Sales]) - [Cost]go deeper
Be ready to say that a formula without an aggregation runs per row and can be used as a dimension, while one with SUM or COUNTD runs once per mark. Recognise the "cannot mix aggregate and non-aggregate arguments" error and both ways to repair it.
Explain the mechanics: row-level expressions are pushed into the query the data source runs, aggregates are computed at the view's level of detail, and non-linear expressions such as ratios give different answers depending on which side of the aggregation you compute them.
Show that you audit dashboards for average-of-ratios bugs and can explain why a KPI disagrees with finance. Know that a filter on a row-level field and a filter on an aggregate remove different things, and that the choice changes every number on the sheet.
Own the question of where each calculation should live. Repeated row-level logic that every workbook redefines belongs upstream in the model, not in a dozen workbook-local fields, and standardising ratio definitions is what stops two dashboards reporting different margins.
## What a calculated field is A calculated field in Tableau is a named formula saved in a workbook or a published data source. Tableau decides *when* to evaluate that formula from its contents: if the formula contains an aggregation function, it is an aggregate calculation and runs after grouping; if it does not, it is a row-level calculation and runs before grouping. Nothing in the dialog forces this choice — the formula you type decides it, which is why the distinction surprises people. ## Row-level calculations Examples: `[Sales] - [Cost]`, `UPPER([Customer Name])`, `IF [Discount] > 0 THEN 'Discounted' ELSE 'Full price' END`, `DATEDIFF('day', [Order Date], [Ship Date])`. Each of these produces one value for every row of the underlying data. Tableau normally pushes the expression into the SQL it sends to the data source (or, in an extract, can pre-compute it), so it costs little. Because a value exists per row, the field can be used exactly like a native column: - as a **dimension** that slices the view and groups marks; - inside an aggregation later, e.g. `SUM([Sales] - [Cost])`; - in a **dimension filter**, which removes rows before any aggregation happens. ## Aggregate calculations Examples: `SUM([Sales])`, `COUNTD([Customer ID])`, `SUM([Profit]) / SUM([Sales])`, `MAX([Order Date])`. These evaluate once per **mark**. A mark is defined by the view's *level of detail*: the combination of dimensions on Rows, Columns, Color, Size, Label, Detail and Tooltip. Put Region on Rows and you get one value per region; add Category and the same formula recomputes per region-and-category cell. The number is therefore a property of the view, not of the data alone. An aggregate result cannot be used as a grouping dimension, because it does not exist until the grouping has already happened. ## Why Tableau refuses to mix them `SUM([Sales]) - [Cost]` raises "Cannot mix aggregate and non-aggregate arguments with this function." The left term yields one number per mark; the right yields one number per row. There is no sensible way to subtract them, so Tableau blocks it rather than guessing. The two legal repairs are: - `SUM([Sales]) - SUM([Cost])` — aggregate both terms; - `SUM([Sales] - [Cost])` — do the arithmetic per row, then aggregate. For a linear expression like subtraction these give the same answer. For anything non-linear they do not, which leads to the classic mistake below. ## Ratio of aggregates versus average of ratios Suppose two orders: profit 1 on sales 10, and profit 4 on sales 90. - `AVG([Profit] / [Sales])` computes each row's ratio (10% and about 4.4%) and averages them: roughly 7.2%. - `SUM([Profit]) / SUM([Sales])` computes 5 / 100 = 5%. Both are arithmetically correct; only the second answers "what is our profit ratio?", because it weights each order by its size. Almost every business ratio — profit ratio, conversion rate, average order value — is a ratio of aggregates. Writing the row-level ratio and averaging it is one of the most common wrong numbers on a Tableau dashboard, and it is invisible unless someone checks a total by hand. ## Filters follow the same split A filter on a row-level field discards rows before aggregation, so the aggregates shrink. A filter placed on an aggregate — a measure filter such as `SUM([Sales]) > 1000` — discards whole marks after aggregation. Same dialog, very different effect on the numbers, and it follows directly from when each kind of calculation runs. ## Where the other two kinds fit Tableau has two more calculation kinds beyond these. A **level of detail expression**, written in braces such as `{FIXED [Customer ID] : SUM([Sales])}`, is an aggregate that declares its own grain instead of inheriting the view's. A **table calculation** such as `RUNNING_SUM(SUM([Sales]))` runs in Tableau on the aggregated result the query already returned. Both return aggregate values, so both obey the mixing rule. ## What interviewers listen for The short version they want: row-level runs per row in the database and can be a dimension; aggregate runs per mark and depends on the view. The follow-through they want: you can name the mixing error, you know both repairs, and you instinctively write a business ratio as a ratio of sums rather than an average of per-row ratios.
- What exactly happens when you write SUM([Sales]) - [Cost] in a calculated field?Tableau refuses to save it with "cannot mix aggregate and non-aggregate arguments": one term produces a value per mark, the other a value per row. Fix it by aggregating both terms, `SUM([Sales]) - SUM([Cost])`, or by doing the arithmetic per row first, `SUM([Sales] - [Cost])`. For subtraction the two agree; for a ratio they do not.
- Which of the two can you use as a dimension to group marks, and why?Only the row-level calculation. It has a value on every underlying row, so Tableau can group rows by it exactly as it groups by a native column. An aggregate calculation has no value until rows have already been grouped, so it lands in Measures and cannot define the grouping. The exception is a FIXED level of detail expression, whose result can be used as a dimension.
- Where does a row-level calculation actually get computed?Normally in the data source: Tableau folds the expression into the SQL it generates, so the database does the work and only aggregated results come back. In an extract, deterministic row-level expressions can be pre-computed and stored, which makes them cheap to reuse. Aggregate calculations are computed in the query's grouping step, not per row.
A row-level calculation is a new column added to every line of a receipt; an aggregate calculation is the subtotal printed at the bottom of whichever group of receipts you happen to be holding.
saying these in an interview costs you the question
- Assumes every calculated field is evaluated once per row
- Writes a business ratio as an average of per-row ratios
- Thinks wrapping a field in SUM is cosmetic
- Tries to fix the mixing error by deleting the aggregation
- Believes an aggregate calculation can be used as a dimension