xmetricErrors

Tableau error Aggregation

The calculation contains errors

Cannot mix aggregate and non-aggregate comparisons or results in 'IF' expressions.

The IF test reads one row, YEAR([Order Date]), but the result is a group total, SUM([Profit]) / SUM([Sales]). The test and the result must both be aggregate or both row-level.

Updated · Checked on the xmetric engine

Which formula raises it, and what fixes it?

Raises the error
IF YEAR([Order Date]) = 2025 THEN SUM([Profit]) / SUM([Sales])
END
Tableau: Cannot mix aggregate and non-aggregate comparisons or results in 'IF' expressions.

The xmetric engine rejects it too.

Fixed
IF ATTR(YEAR([Order Date])) = 2025 THEN SUM([Profit]) / SUM([Sales])
END

Per Region, on the 3 example rows, it returns:

RegionResult
East0.2
West−0.07

Why it works. ATTR turns the test into an aggregate: it returns the year when every row in the mark shares it, and * when they differ. If a mark can span several years, use MIN or MAX instead, or move the test inside the sums: SUM(IF YEAR([Order Date]) = 2025 THEN [Profit] END) / SUM(IF YEAR([Order Date]) = 2025 THEN [Sales] END).

The 3 example rows
RegionOrder DateSalesProfit
East2025-01-05204
West2025-02-1010-2
West2025-03-0151

What else raises this message?

  1. Raises it

    IF [Region] = 'East' THEN SUM([Profit]) END

    Fixed

    IF ATTR([Region]) = 'East' THEN SUM([Profit]) END

    Region is on the view, so ATTR gives the mark's region and the test becomes aggregate like its result.

    Checked on the xmetric engine: the first fails, the fix runs.

  2. Raises it

    IF [Profit] > 0 THEN SUM([Sales]) END

    Fixed

    SUM(IF [Profit] > 0 THEN [Sales] END)

    To add only the profitable rows, test each row inside the SUM.

    Checked on the xmetric engine: the first fails, the fix runs.

Does my formula have this problem?

Paste your formula and run it on the example rows. It uses the fields [Region], [Order Date], [Sales], [Profit]; rename yours to match.

Runs in your browser on the xmetric engine. Nothing is uploaded.

Which functions does this involve?

What do people ask about this error?

Which fix is right: ATTR on the test, or IF inside SUM?

They answer different questions. ATTR tests the mark (is this region East?). IF inside SUM tests each row (add the rows where profit is positive). Pick the one that matches the question.

Why does Tableau allow aggregates in the THEN branch but not mixed with the test?

It doesn't allow either mix: test and results must all be at the same level. The IF as a whole becomes aggregate only when every part is.

Where is this message documented?