Aggregation Type Average (AVG) - How to do it!

[Former Member]

Hello,

I created a local model y BOC using a SQL connection. I need to setup some measures to aggregate as Average not Sum. Like Unit Price, Discount % or other measures that don't make sense adding up and must be rolled up as average from the very detail. Still, I can't figure out the right combination of aggregation type, exception aggregation type, etc. I mostly get sum results.

Any advice on how to do this is greatly appreciated!

[JohnL · Accepted solution]

Hi Ricardo,

Exception aggregation works like this.

If you keep SUM as the aggregation type, then the account will sum for any dimension except for the dimension defined as the Exception aggregation dimension.

In my example below, I have PRD_GRP set as the exception aggregation dimension so it it sums for the REGION dimension (top screen) but performs an average for the PRD_GRP dimension (bottom screen).

The Exception Aggregation takes effect only when that dimension is referenced in the Row or Column of your table.

HTH

[Former Member · Accepted solution]

Thanks John, your answer clarifies the feature very nicely!

[former_member308471]

Hi Ricardo,

Even I am facing the same issue while working with Average.

So I thought its best to implement it directly at Story level using Calculation Editor.

Create a new chart -> Create a new calculation and select Type: Aggregation

Regards,

Kiran Shenvi

[Former Member]

Thanks Kiran, this is what I ended up doing.

The other aggregation exception feature has some quirks to it, a limit when you try to add many exceptions dimensions. I hope this gets fixed or improved in upcoming versions, non additive metrics are a-must in OLAP models/cubes.