xmetricErrors

Tableau error Table calculation

The calculation contains errors

All fields must be aggregate or constant when using table calculation functions or fields from multiple data sources.

A table calculation such as LOOKUP works on the marks in the view, not on rows. Every field it touches must be aggregated first, so a bare [Sales] inside LOOKUP fails.

Updated · Checked on the xmetric engine

Which formula raises it, and what fixes it?

Raises the error
LOOKUP([Sales],-1) + SUM([Sales])
Tableau: All fields must be aggregate or constant when using table calculation functions or fields from multiple data sources.

The xmetric engine rejects it too.

Fixed
LOOKUP(SUM([Sales]),-1) + SUM([Sales])

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

RegionValueResult
East20null
West1535

Why it works. SUM([Sales]) gives one value per mark, and LOOKUP can step from mark to mark. Here Region is the addressing dimension, ascending, in one partition; a different order changes the result.

The 3 example rows
RegionSales
East20
West10
West5

What else raises this message?

  1. Raises it

    RUNNING_SUM([Sales])

    Fixed

    RUNNING_SUM(SUM([Sales]))

    Table calculations work on the marks in the view, so their argument must be an aggregate.

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

  2. Raises it

    [Sales] - WINDOW_AVG(SUM([Sales]))

    Fixed

    SUM([Sales]) - WINDOW_AVG(SUM([Sales]))

    Compare the mark's own sum with the window average; both sides now live at the mark level.

    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], [Sales]; 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?

What makes a calculation a table calculation?

Any of LOOKUP, RUNNING_, WINDOW_, INDEX, RANK, FIRST, LAST, SIZE, TOTAL and PREVIOUS_VALUE. Once one appears, everything else in the formula has to be an aggregate or a constant.

Can I use a table calculation inside a FIXED expression?

No. Level of detail expressions are computed before table calculations, so the order makes it impossible. Compute the LOD first and apply the table calculation on top.

Where is this message documented?