Regular Aggregate Property for Model Calculations in Framework Manager

For ratio-related calculations, it is necessary to apply aggregation function before the arithmetic operation take place. For example, column Margin is calculated from the other facts Revenue and Product Cost by setting Regular Aggregate property to Sum.

Margin = (Revenue - Product cost) / Revenue

 

When we build a report to show Margin, it may give incorrect results as shown below:

 

This is happened because of incorrect order of aggregation applied to the columns. 

Margin = sum ((Revenue - Product cost) / Revenue)

In order to get correct results, aggregation should have been applied prior to arithmetic operation.

Margin = (sum (Revenue) – sum (Product cost)) / sum (Revenue)

This can be accomplished by creating a standalone calculation in framework manager and set the Regular Aggregate property to Calculated. Please note that, stand-alone calculations cannot be used in Analysis Studio. Instead, use an embedded calculation in the measure dimension.

 

Please note that, Regular Aggregate property set to Calculated is not supported for the calculations that are embedded within the query subject. It is supported only for standalone calculations and for the calculations that are embedded within Measure dimensions that are created based on the measures from the same measure dimension as shown below. 

Margin = sum (Revenue - Product cost) / sum (Revenue)