Calculated Fields and LOD

## 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.