locked
Roll-up (parent) row not showing correct data when calculating moving averages RRS feed

  • Question

  • Here's the scenario:

    I have a datapoint (Dism) that is correctly totaling.

    I have a second field which is correctly calculating the trailing 13 week average (TTWA) at the child level (individual plants).

    HOWEVER - at the parent level (region), Excel is averaging the TTWAs of the children instead of calculating the TTWA of the parent.  So instead of the TTWA of the region being around 400, it's being calculated as 60 (which the average of all the children).

    Here is the formula I'm using for TTWA column:

    ttwaDISM:=CALCULATE(
    AVERAGEX(WPRdata,totalDism]),
    DATESINPERIOD(BasicCalendarUS[DateKey],LASTDATE(BasicCalendarUS[DateKey]),-91, DAY))

    Again, it is correctly calculating the trailing 13 week average for the individual children.  It's the sub-total for the region that is incorrect.

    Any help on this would be greatly appreciated.

    (sorry, MS will not allow me to post the picture I had which illustrates the issue)

    Tuesday, May 16, 2017 4:50 PM

Answers

  • So an average is nothing more than a sum divided by a count.

    You code simply says that within the current filter context you want to sum the totalDism column and divide by the number of rows in the WPRData table that are in the same filter context, So unless you only have 1 row per plant, both your averages are probably not what you are after. 

    Usually to calculate a rolling average I sum the rolling period and divide by the number of weeks in the period. The only catch that you need to cater for are the first periods of data where you don't have a full 13 weeks history, I've done a count of the days, divided by 7 and rounded to the nearest week, but you may want to adjust this (or even add some IF logic to null out the data if there is not a full 13 weeks of history)

    ttwaDISM:=CALCULATE(
    DIVIDE(SUM(WPRdata[totalDism]) ,
    round(COUNTROWS(DATESINPERIOD(BasicCalendarUS[DateKey],LASTDATE(BasicCalendarUS[DateKey]),-91, DAY))/7),0),
    DATESINPERIOD(BasicCalendarUS[DateKey],LASTDATE(BasicCalendarUS[DateKey]),-91, DAY))


    http://darren.gosbell.com - please mark correct answers

    Wednesday, May 17, 2017 12:20 AM

All replies

  • So an average is nothing more than a sum divided by a count.

    You code simply says that within the current filter context you want to sum the totalDism column and divide by the number of rows in the WPRData table that are in the same filter context, So unless you only have 1 row per plant, both your averages are probably not what you are after. 

    Usually to calculate a rolling average I sum the rolling period and divide by the number of weeks in the period. The only catch that you need to cater for are the first periods of data where you don't have a full 13 weeks history, I've done a count of the days, divided by 7 and rounded to the nearest week, but you may want to adjust this (or even add some IF logic to null out the data if there is not a full 13 weeks of history)

    ttwaDISM:=CALCULATE(
    DIVIDE(SUM(WPRdata[totalDism]) ,
    round(COUNTROWS(DATESINPERIOD(BasicCalendarUS[DateKey],LASTDATE(BasicCalendarUS[DateKey]),-91, DAY))/7),0),
    DATESINPERIOD(BasicCalendarUS[DateKey],LASTDATE(BasicCalendarUS[DateKey]),-91, DAY))


    http://darren.gosbell.com - please mark correct answers

    Wednesday, May 17, 2017 12:20 AM
  • Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Simplified by eliminating WPRdata,totalDism] (sic). See Darren's correct syntax.
    Region sub-total now uses HasOneValue() and Sum().
    Period is plain 30 days, no weeks.
    http://www.mediafire.com/file/3vj90ykyx7a898k/05_16_17b.xlsx
    http://www.mediafire.com/file/9h771a10xm6w014/05_16_17b.pdf

    Wednesday, May 17, 2017 2:23 AM
  • I am going to mark the first answer as the solution.  Worked like a charm!

    However, PowerPivot threw an error when I copy & pasted it - the Round() function was missing the second argument.  Also - I ended up using the original column instead of the measure which summed up the 'Dism' column

    ttwaTEST:=CALCULATE(DIVIDE(SUM([Dism]) ,round(COUNTROWS(DATESINPERIOD(BasicCalendarUS[DateKey],LASTDATE(BasicCalendarUS[DateKey]),-91, DAY))/7,1),0),DATESINPERIOD(BasicCalendarUS[DateKey],LASTDATE(BasicCalendarUS[DateKey]),-91, DAY))

    thank you SO much for the help!

    Wednesday, May 17, 2017 12:51 PM