Aggregation and the order of operations

In the modeling tools and query tools, you can perform a calculation and then aggregate the results, or aggregate the values in the calculation first and then perform the calculation.

To control this functionality, you must set the aggregation properties accordingly. To calculate first and then aggregate, set the column Aggregate property to Sum or Total (depending on the user interface).

To aggregate first and then calculate, set the Aggregate property to Calculated.

For more information about the Aggregate property, see Object properties.

If the Calculate after aggregation checkbox is provided, selecting it sets the Aggregate property to Calculated.

Note: The Calculated setting applies only to stand-alone (selectable) calculations in Framework Manager, which are created outside of a query subject. For data modules, the Calculate after aggregation setting for calculations applies to stand-alone calculations as well as calculations embedded in a table. The query tools, such as Dashboarding or Reporting, also allow authors to create calculations and control the order of operations by setting aggregation to Calculated in Reporting, and setting the Calculate after aggregation setting in Dashboarding.

The following table shows the different outcomes when calculating values before or after aggregation.

Table 1. Calculating values before and after aggregation
Row number A B A * B (set to Sum) A*B (set to Calculated)
1 5 10 50 50
2 10 5 50 50
Total 15 15 100 225

In the A*B (set to Sum) column, the calculation is performed first and then the values are summed (50+50=100). In the A*B (set to Calculated) column, the detail rows are aggregated first (15 for each total) and then the results are multiplied (15*15 = 225).

Example: Impact of aggregation settings on calculations

The Groceries data module contains the following 3 measures: Quantity, UnitPrice, and C_Price.

Groceries data module

The property values for each measure are shown in the following tables:

Table 2. Quantity
Property Value
Label Quantity
Expression Quantity
Usage Measure
Aggregate Total
Table 3. UnitPrice
Property Value
Label UnitPrice
Expression UnitPrice
Usage Measure
Aggregate Average
Table 4. C_Price
Property Value
Label C_Price
Expression Quantity * UnitPrice
Usage Measure
Calculate after aggregation False
Aggregate Total

The following screen capture shows the Groceries data module in the grid view:

Groceries data module in grid view

The following calculations are created to demonstrate the impact of the aggregate settings. The calculations with the “bad” label demonstrate areas where problems can occur:

  • C_Produce
  • C_Produce bad
  • C_Drinks
  • C_Drinks bad

The property values for each calculation are shown in the following tables:

Table 5. C_Produce
Property Value
Label C_Produce
Expression case when Sheet1.Type = 'Produce' then Sheet1.C_Price else 0 end
Usage Measure
Calculate after aggregation False
Aggregate Total
Table 6. C_Produce bad
Property Value
Label C_Produce bad
Expression case when Sheet1.Type = 'Produce' then Sheet1.C_Price else 0 end
Usage Measure
Calculate after aggregation True
Aggregate Calculated
Table 7. C_Drinks
Property Value
Label C_Drinks
Expression case when Sheet1.Type = 'Drinks' then Sheet1.C_Price else 0 end
Usage Measure
Calculate after aggregation False
Aggregate Total
Table 8. C_Drinks bad
Property Value
Label C_Drinks bad
Expression case when Sheet1.Type = 'Drinks' then Sheet1.C_Price else 0 end
Usage Measure
Calculate after aggregation True
Aggregate Calculated

Here is a report based on the Groceries data module. The Summary row shows different results depending on how the calculations were created.

Report based on the Groceries data module

For more information, see the related documentation for the following components: