KNOWLEDGE BASE

Error "Cannot mix aggregate and non-aggregate arguments with this function" When Creating a Calculated Field

Published: 11 Jan 2016

Issue

When creating a calculation, the following error might occur:

"Cannot mix aggregate and non-aggregate arguments with this function."

Tableau Desktop

Resolution

Modify the calculation so that all fields are either aggregate or non-aggregate. Each option can result in different values (please reference the additional information section for specific examples).

Option 1 (Aggregate Then Divide)

Wrap all fields in an aggregation.

Sample:
[Profit] / SUM ([Sales]) -> SUM ([Profit]) / SUM ([Sales])

Option 2 ( Divide Then Aggregate)

Remove aggregations from all of the fields.

Sample:
[Profit] / SUM ([Sales]) ->[Profit] / [Sales]

Option 3 ( Condition Then Aggregate)

Move the aggregation so all fields are aggregated.

For example, the calculation:
IF [Row ID] = 1
THEN SUM( [Sales] )
END

could become:
SUM(
IF [Row ID] = 1
THEN [Sales]
END )

Option 4 (Convert to Non-Aggregate Then Aggregate)

Use the FIXED Level of Detail Expression to make an aggregation non-aggregate.

[Sales]/SUM( [Sales] ) -> [Sales]/{FIXED: SUM( [Sales] )}

Cause

Aggregations are computed at the level of detail in the view,  which will return one value for several records. Non-aggregate fields are computed for every record in the underlying data, which will return one value per record.

Tableau Desktop would not know how to compare the one value of the aggregation to the several values of the non-aggregated field.

Sample Data Set
Row IDProfitSales
110030
25060
3710

Option 1

SUM ([Profit]) / SUM ([Sales])

Result:
(100 + 50 + 7) / (30 + 60 + 10) =  157/100  = 1.57

Option 2

[Profit] / [Sales]

Result: (assuming that the aggregation in the view is SUM)

100/30 + 50/60 + 7/10 = 3.333 + .833 + .7 = 4.867

Result:
30 + 0 + 0 = 30

Option 4 (https://onlinehelp.tableau.com/current/pro/desktop/en-us/calculations_calculatedfields_lod_overview.html)

Level of Detail expressions always return non-aggregate results and can be used to specify the level the aggregation occurs at.

[Sales]/{FIXED: SUM( [Sales] )}

Result: (assuming that the aggregation is sum)

30/100 + 60/100 + 10/100 = 3+60+10 = .3 + .6 + .1 = 1