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)
