Answered by:
Rollup (parent) row not showing correct data when calculating moving averages
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 subtotal 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
 Proposed as answer by Angelia ZhangMicrosoft contingent staff Wednesday, May 17, 2017 3:34 AM
 Marked as answer by Rob.W Wednesday, May 17, 2017 12:51 PM
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
 Proposed as answer by Angelia ZhangMicrosoft contingent staff Wednesday, May 17, 2017 3:34 AM
 Marked as answer by Rob.W Wednesday, May 17, 2017 12:51 PM
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 subtotal 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.pdfWednesday, 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