Skip to Content

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

Feb 16, 2017 at 11:33 PM


avatar image


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!

aggregationboc.png (44.8 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
John Leggio
Mar 01, 2017 at 11:02 PM

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.


Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks John, your answer clarifies the feature very nicely!

Kiran Shenvi Feb 25, 2017 at 08:38 AM

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


Kiran Shenvi

average.jpg (24.9 kB)
Show 1 Share
10 |10000 characters needed characters left characters exceeded

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.