Aggregate Error Messages In a Calculation Editor

Julkaisupäivä: Sep 11, 2025

Kuvaus

When creating a calculation, one of the following errors might occur:

"Cannot mix aggregate and non-aggregate arguments with this function." (Option 1,2,3 or 4 can be used).
"All fields must be aggregate or constant when using table calculation functions or fields from multiple data sources." (Option 1 or 3 can be used).
"Argument to sum (an aggregate function) is already an aggregation, and cannot be further aggregated." (Option 2, 3 or 4 can be used).

Cause

"Cannot mix aggregate and non-aggregate arguments with this function."
- All fields must be the same aggregation (aggregated or de-aggregated). 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.

"All fields must be aggregate or constant when using Table Calculations or fields from multiple data sources"
- When blending or using table calculations, all fields must be aggregated.

"Argument to sum (an aggregate function) is already an aggregation, and cannot be further aggregated.".
- An aggregated field cannot be aggregated again.

Ratkaisu

Each option can result in different values (please reference the attached workbook at the bottom of this article and additional resources section for specific examples).

Option 1 (Aggregate All Fields)

Wrap all fields in an aggregation.

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

Sample (using LOD):
RANK( {fixed: SUM([Sales])} ) --> RANK( SUM( {fixed: SUM([Sales])} ))

Option 2 ( De-aggregate All Fields)

Remove aggregations from all of the fields.

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

Sample (using LOD):
[Profit] / SUM ([Sales]) ->[Profit] / { fixed: SUM ([Sales]) }

Option 3 ( De-aggregate All Fields Then Aggregate the Calculation.)

Move the aggregation so the calculation is 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 (Use Level of Detail to De-aggregate a Field)

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] )}

Lisäresurssit

How to determine if a field is aggregated:

Imported fields from the underlying data and Level of Detail (LOD) expressions are always non-aggregated until they are wrapped in an aggregation, such as SUM(), MIN(), ATTR(). To see a full list of all aggregated functions, please look at the "Aggregate Functions" article in the related links section.

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.

One trick to determine if a field is aggregated is to add the field into the view. If the field displays AGG("Field Name"), the field is already aggregated

Examples:

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

Sample Data Set

Row ID	Profit	Sales
1	100	30
2	50	60
3	7	10

Option 1

Formula:
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

See Level of Details Calculations

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

Knowledge-artikkelin numero

001473080

Liitteet

Aggregate Sample Workbook.twbx

19 KB

Ratkaisiko tämä artikkeli ongelmasi?

Anna palautetta, jotta voimme kehittyä!