none
(Moving) Average of an Absolute Measure

    Question

  • Hi there,

    I am just starting out with Powerpivot and am having difficulties calculating the moving average of an absolute measure.

    My data in Table1 has positive and negative values for the deviation between forecasted numbers and actual numbers for different groups and dates. For any given date, the deviations within groups can offset each other, so as a first step I created a pivot table which just sums all deviations.

    As a next step I calculate the absolute of the summed up deviations: AbsDev:=ABS([Sum of Deviations]), which displays correctly in the pivot table.

    Now I am trying to build a 3 month moving average using the formula:

    ([AbsDev]datesbetween(Table1[Period],Firstdate(Dateadd(Table1[Period],-11,Month)),Lastdate(Table1[Period]))))

    This calculates a moving average, but it is the absolute average of the deviation number, not the average of the absolute deviation number.

     As an example, my pivot table looks like this

    Period  Sum of Deviation  Abs Deviation

    1                         10                     10

    2                        -25                     25

    What I get with the above formula is a value of 7.5, i.e. the absolute of value of the average of 10 and -25. What I want is a value of 17.5, i.e. the average of the absolute values of 10 and 25.

    Any ideas what I am doing wrong and how this could be resolved?

    Wednesday, June 19, 2013 5:18 PM

Answers

  • Hi DerNikolaus,

    if Period column is number format. You can try measure as below:

    IF(HASONEVALUE(Table1[Period]),
       CALCULATE(
            AVERAGEX(
                VALUES(Table1[Period]),
                [ABS Deviation]
            ),
            Table1[Period]<=VALUES(Table1[Period]) &&
            Table1[Period]>VALUES(Table1[Period]) -3
        )
    )
    Regards,

    • Marked as answer by DerNikolaus Friday, June 21, 2013 12:54 PM
    Thursday, June 20, 2013 3:27 PM

All replies

  • Hi DerNikolaus,

    if Period column is number format. You can try measure as below:

    IF(HASONEVALUE(Table1[Period]),
       CALCULATE(
            AVERAGEX(
                VALUES(Table1[Period]),
                [ABS Deviation]
            ),
            Table1[Period]<=VALUES(Table1[Period]) &&
            Table1[Period]>VALUES(Table1[Period]) -3
        )
    )
    Regards,

    • Marked as answer by DerNikolaus Friday, June 21, 2013 12:54 PM
    Thursday, June 20, 2013 3:27 PM
  • Hi CDzung,

    thank you so much, this is working perfectly. Have a nice weekend!

    Friday, June 21, 2013 1:01 PM