Aggregations in Formulas
By default, when formulas include aggregated quantities, aggregation is performed before the formula calculations. Use exception aggregation if you want the aggregation done after the calculations.
SAP Analytics Cloud first attempts to aggregate the data as much as possible, and then processes any calculations. If no exception aggregation is defined, this process applies to all dimensions of the model. If exception aggregation is defined, the dimensions that are defined as exception aggregation dimensions are excluded from this processing. The exception aggregation is then processed after the calculations.
Because exception aggregation is done last, you can specify exception aggregation to ensure that the aggregation is done after the formula calculations.
For example, if you have the formula Total Income = Price * Volume, you might want to ensure that the Total Income amounts are summed after the Price * Volume calculations are done. In this case, use the SUM exception aggregation type for Total Income, and specify all of the available dimensions as exception aggregation dimensions:
Exception Aggregation with Properties
In the new model type, you can optimize formulas containing an exception aggregation by specifying dimensions properties. When you create a formula using a dimension property, both the Exception Aggregation Type and Exception Aggregation Dimensions fields are automatically pre-filled. By default, the application sets the Exception Aggregation Type to SUM, and populates the Exception Aggregation Dimensions field with the dimension properties referenced in the formula.
These changes can also be seen within the Advanced Formula Editor in the Model Structure workspace, with Exception Aggregation Type set to SUM, and the Exception Aggregation Dimensions field populated with the dimension properties. This allows for better performance and simplifies the overall exception aggregation workflow.
While this enhancement provides more flexibility to add properties in the exception aggregation, having too many properties can potentially hinder performance. As a best practice, we recommend having exception aggregations on the level you need with relevant properties only.
In the Calculations workspace, after you’ve created the formula, you should see the updated aggregated values with the exception aggregation without having to add the dimension property to the drill. If needed, you can add additional dimensions properties using the multi-selection dialog icon next to the Exception Aggregation Dimensions option.