Skip to Content
avatar image
Former Member

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

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!

aggregationboc.png (44.8 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    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.

    HTH

    Add comment
    10|10000 characters needed characters exceeded

  • 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

    Regards,

    Kiran Shenvi

    Add comment
    10|10000 characters needed characters exceeded

    • 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.