KNOWLEDGE BASE

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


Published: 11 Jan 2016
Last Modified Date: 17 Aug 2018

Issue

When creating a calculation, the following error might occur: 

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

Environment

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 a Level of Detail (LOD) Expression to make an aggregation non-aggregate. All LOD expression return non-aggregated values.

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

Additional Information

Imported fields and Level of Detail (LOD) expressions are always non-aggregated until they are wrapped in an aggregation, such as SUM(), MIN(), ATTR(), etc... Calculated fields can be either aggregate or non-aggregate depending on how aggregations are used. If no aggregations are used, or if the outer most expression is a LOD expression, then the calculation will return non-aggregated results.

Below is an explanation of how each option works using the sample data set shown as a reference.

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
 

Option 3

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


 
Did this article resolve the issue?