Tableau’s Annoying “Aggregate and Non-Aggregate” Error, Explained with Excel

It doesn’t take long for a new Tableau user to hit the “cannot mix aggregate and non-aggregate comparisons or results” error when writing a calculation. What’s going on? Is SUM() in the wrong place? Are you supposed to use ATTR()? What’s the best workaround, and what the heck does that error message mean, anyway?

Don’t panic: Experienced Tableau users also get this error all the time. Once you understand what Tableau is doing, it’ll be clear how to fix it. The easiest way to explain what’s going on is with Microsoft Excel.

Tableau’s Two Modes

Tableau has two modes of calculation:

• Non-aggregated: Happens on each individual row of the data
• Aggregated: Happens across all rows of the data

Let’s take a simple example in Excel, using a worksheet with food items, types, and quantities. Say you want to multiply [Quantity] by 1.5.

• A non-aggregated calculation works with cells in a single row (going across): C2 * 1.5, C3 * 1.5, etc.
• An aggregated calculation works with a range across several rows (going down a column): SUM(C2:C6) * 1.5.

Note that the constant 1.5 doesn’t affect whether the calculation is aggregated or not.

Why You Get the Error

Tableau needs to handle everything non-aggregated (row-level) first, and then aggregate across rows (column-level) at the end.

As another example, let’s say you only want to sum up the fruit. This calculation will cause an error in Tableau:

IF [Type] = “Fruit” THEN SUM([Quantity]) ELSE 0 END

The first part is trying to do something on each individual row, but the second part is trying to calculate across all rows. Tableau can’t do both at the same time. It’s a little like trying to write this formula in Excel:

=IF(B2:B6 = “Fruit”, SUM(C2:C6), 0)

Excel will let you do this, but it will “spillover” into other cells and doesn’t give a meaningful result. Instead, it applies the IF to the individual rows but ignores the IF for the sum, returning (10 + 20 + 30 + 40 + 50 = 150).

How to Fix It the Error in Tableau

Step 1: Do the non-aggregated (row-level) calculation. Do not use SUM(), AVG(), and other functions that work across the data set. For our example in Tableau, this would be

[Fruit Quantity]:

IF [Type] = “Fruit” THEN [Quantity] ELSE 0 END

And in Excel would be

=IF(B2=”Fruit”, C2, 0)

Step 2: Do the aggregated (column-level) calculation.

In Tableau, there are two options:

Option 1. Drag the non-aggregated field onto the view. Tableau will automatically aggregate it with SUM() by default.

Tableau likes to aggregate things, so this is how it treats all measures that don’t already have an aggregation as part of the formula.

Option 2. Wrap the non-aggregated part of your calculation inside an aggregation:

[Fruit Quantity (Agg)]:

SUM(

IF [Type] = “Fruit” THEN [Quantity] ELSE 0 END

)

If you drag this field to the view, Tableau will treat it the same but will not instead show AGG() on the green pill (because it is already aggregated).

In Excel, both of these options are akin to doing the formula on each row, then using SUM() on the resulting vertical range of cells.

Why ATTR() Isn’t a Magic Bullet

Often, folks suggest fixing the error by wrapping the dimensions in ATTR().

IF ATTR([Type]) = “Fruit” THEN SUM([Quantity]) ELSE 0 END

This calculation won’t give you an error, but in many cases it won’t give you the right result.

ATTR() is a special function that means “If all the values are the same, return that value. Otherwise, return * [an asterisk]”. Here, it will work on rows for individual food items (which have only one Type per row), but will return 0 for the grand total (where both fruits and vegetables Types are included).

FIXED LOD: A Special Case

Don’t you wish you could have it both ways: do an aggregation across multiple rows, and then use the result in a calculation for each individual row? That’s where the FIXED Level of Detail calculation comes in.

Let’s say you want the want the “Fruit” and “Vegetable” totals available on the row level so that you can next calculate a percentage. In Excel this can be done using the SUMIF function, which looks like this:

=SUMIF($B$2:$B$6, B2, $C$2:$C$6)

In Tableau, the calculation looks like this:

[Type Total]:

{FIXED [Type] : SUM([Quantity])}

The result goes back to being non-aggregated. It’s okay to then create a row-by-row percentage (also non-aggregated) like this:

[Percent of Type]:

[Quantity] / [Type Total]

By the way, these types of totals and percentages can also be done with Table Calculations. Depending on the specifics of your viz, a Table Calc or a FIXED LOD may be the easier approach.

A Related Error

You may also encounter the error “Argument to XXX(an aggregate function) is already an aggregation, and cannot be further aggregated”.

This means that you tried to use one aggregation on top of another. For instance, let’s say you want to know the average total for fruits only, and using Option 2 you create this field:

[Fruit Quantity (Agg)]:

SUM(

IF [Type] = “Fruit” THEN [Quantity] ELSE 0 END

)

You then try to follow with this calculation:

[Fruit Quantity Average]:

AVG([Fruit Qty (Agg)])

Tableau won’t allow you to do AVG(SUM([x])). Instead, Table Calculations are typically used for this. Table calcs go one level higher (here, performing an average of sums), but they are dependent on how the field is used in the view. One valid possibility would be

[Fruit Quantity Average]:

WINDOW_AVG([Fruit Qty (Agg)])

Some people also use Fixed LODs for this type of calculation, but table calculations are usually better at avoiding duplicate values.

Final Thoughts

As I mentioned above, even Tableau experts hit aggregation errors from time to time. Just remember to work row-by-row (non-aggregated) first, then aggregate (with SUM, AVG, etc.), and you’ll be able to fix it every time. Good luck!