+ Reply to Thread
Results 1 to 9 of 9

Powerpivot Rolling Average Calculated Measure

  1. #1
    Registered User
    Join Date
    07-15-2013
    Location
    Denver,CO
    MS-Off Ver
    Excel 2010
    Posts
    9

    Powerpivot Rolling Average Calculated Measure

    I am very new to powerpivot and DAX functions and I need to calculate a rolling average without using any of the date/time DAX functions as I do not have any dates, I only have a value representing a # of days. All I can find is rolling average instructions using the specific date functions and can't figure out how to modify for my needs.

    I have a Column that has values 0,1,2,3,...... representing a number of days. I have a column with corresponding production values for each of those days. I need to know the 7 day average for each #of days. Obviously the first 7 days won't be a 7 day average - because there won't be seven days worth of production data. So up to the first seven days I just want the 1 day average for day 1, 2 day average for day 2, 3 day average for day 3, etc.

    Any suggestions would be extremely helpful.

    Thank You.

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Powerpivot Rolling Average Calculated Measure

    do you want the average of the total daily production for each of the last seven days, or the average production amount in your source table for the last 7 days-i.e. the average of the individual table rows, or the average of the daily totals?

    a sample workbook with an excel table representative of the real source table data (censored as need be) would be helpful
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Registered User
    Join Date
    07-15-2013
    Location
    Denver,CO
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Powerpivot Rolling Average Calculated Measure

    I am not sure I understand the question. I tried to atach my file - but it is too big. So I attached a sample file. The Table I have included here is a pivot table from powerpivot and each of the columns have been created using a calculated measure.

    What I would like is another calculated measure that takes the No Zeros Average Oil Rate and gives me the seven day average in column H.

    These are the results I would like.

    So Day 0 will just be 98
    Day 1 - (222+98)/2
    Day 2 - (279+22+98)/3
    Day 3 - (245+279+222+98)/4
    Day 4 - (246+246+279+222+98)/5
    Day 5 - (263+246+245+279+222+98)/6
    Day 6 - (294+263+246+245+279+222+98)/7
    Day 7 - (312+297+263+246+245+279+222)/7
    Day 8 - (294+312+297+263+246+245+279)/7
    Day 9 - (246+294+312+297+263+246+245)/7
    etc......


    Does that makes sense?

    PowerPivotHelp.xlsx

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Powerpivot Rolling Average Calculated Measure

    ok so you want the average of the totals. can you strip the file down to a smaller sample of the data and post that so we can see the real data structure and the current measures?

    in case you can't provide a file, the formula should be something like this

    if(countrows(values(Table1[Days])) = 1,
    CALCULATE(
    AVERAGEX(VALUES(Table1[Days]), Table1[Sum of No Zeros Average Oil Rate])
    ,Table1[Days] <= VALUES(Table1[Days]) && Table1[Days] > VALUES(Table1[Days])-7
    )
    , blank())

    replacing Table1 with your actual table name
    Last edited by JosephP; 07-15-2013 at 12:07 PM.

  5. #5
    Registered User
    Join Date
    07-15-2013
    Location
    Denver,CO
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Powerpivot Rolling Average Calculated Measure

    Okay- The powerpivot table nozeros apparently isn't linked to my source data table - which I didn't realize until just now when I updated the source data and powerpivot table didn't update. But - I scaled down the sourcedata to only include 2013 dates and that seemed to make it small enough to attach.

    So the No Zeros table is just filtered to only include 2013 so it matches the source data even though they aren't actually linked. I will fix that later - I am not sure if there is a way to link it after the fact- but that is beside the point.

    Measure 1 is where I was attempting to create the 7 day average.

    Thanks bunches if you can figure this out I know it is kind of a mess.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-15-2013
    Location
    Denver,CO
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Powerpivot Rolling Average Calculated Measure

    Formula looks great - one question.

    Do I make the Sum of No Zeros Average Oil Rate another calculated measure? Or can I do that right in this formula? My No Zeros Average Oil Rate is the result of another calculated measure so I am not sure where to do this sum?

    if(countrows(values(Table1[Days])) = 1,
    CALCULATE(
    AVERAGEX(VALUES(Table1[Days]), Table1[Sum of No Zeros Average Oil Rate])
    ,Table1[Days] <= VALUES(Table1[Days]) && Table1[Days] > VALUES(Table1[Days])-7
    )
    , blank())

  7. #7
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Powerpivot Rolling Average Calculated Measure

    I'd keep it as a separate measure

  8. #8
    Registered User
    Join Date
    07-15-2013
    Location
    Denver,CO
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Powerpivot Rolling Average Calculated Measure

    It doesn't seem to allow that.

    This is my formula and it returns an error. I tried without the calculate function as well - which also returned an error. I am assuming because the sum function wants a column and not a calculated measure but I am not sure. Any ideas?


    Sum of No Zeros Average Oil Rate:=CALCULATE(SUM('No Zeros'[No Zeros Average Oil Rate]))

  9. #9
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Powerpivot Rolling Average Calculated Measure

    for your measure the formula oughta be

    =if(countrows(values('No Zeros'[Days])) = 1,
    CALCULATE(
    AVERAGEX(VALUES('No Zeros'[Days]), 'No Zeros'[No Zeros Average Oil Rate])
    ,'No Zeros'[Days] <= VALUES('No Zeros'[Days]) && 'No Zeros'[Days] > VALUES('No Zeros'[Days])-7
    )
    , blank())

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 06-22-2012, 07:13 AM
  2. Replies: 4
    Last Post: 05-23-2012, 03:22 PM
  3. PowerPivot Measure Formula
    By Twi78 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-28-2012, 08:45 AM
  4. Rolling totals calculated based on two maybe 3 variables
    By dotsofcolor in forum Excel General
    Replies: 3
    Last Post: 03-05-2012, 12:48 AM
  5. Replies: 1
    Last Post: 11-29-2011, 10:52 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1