Forum Discussion

JonathanJohns's avatar
JonathanJohns
Helper III
9 years ago
Solved

Running Average Calculation

Good morning,   I have a question about the calculation of a running average. I know how to calculate a running average every three weeks with this DAX formula :    Running Average = IF(NewTable[...
Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi JonathanJohns,

     

    In your scenario, you can create calculated columns below:

     

    WeekNum = VALUE(RIGHT('Table1'[Week],2))
    Rank = RANKX(
         FILTER(
         ALLSELECTED(Table1),Table1[Recette]=EARLIER(Table1[Recette])
                   ),
         'Table1'[WeekNum],
     ,1)
    RunningTotal = CALCULATE(SUM('Table1'[Average]),ALLEXCEPT(Table1,'Table1'[Recette]),'Table1'[Rank]<=EARLIER(Table1[Rank]))
    LastVal = var l=LOOKUPVALUE(Table1[RunningTotal],'Table1'[Index Recette],'Table1'[Index Recette],'Table1'[Rank],'Table1'[Rank]-3)
return 

IF('Table1'[Rank]>3,('Table1'[RunningTotal]-l)/3,BLANK())

     

    Measure = IF(MAX('Table1'[Rank])<3,0,IF(MAX('Table1'[Rank])=3,CALCULATE(AVERAGE(Table1[Average]),FILTER(ALL(Table1),'Table1'[Recette]<=MAX('Table1'[Recette])),'Table1'[Rank]<=3),MAX('Table1'[LastVal])))

     

     

     

    Best Regards,
    Qiuyun Yu

Anonymous's avatar
Anonymous
Not applicable
9 years ago

But if you add Recette to your visual... it should work fine?

 

In your most recent table example, with no recette, what do you want the measure to return?

JonathanJohns's avatar
JonathanJohns
Helper III
9 years ago

I added Recette to my visual but the calculation doesn't change.

 

For example :

 

 

In this table, the calculation of the running average is wrong. It doesn't calculate the running average for the two first weeks so that works but the calculation of the third week is wrong. I wanted this result : (60,29+60,60+60,19)=60,36 so not 60,31. That is the same for the next calculations.

 

And now an other example :

 

 

I changed the value of the filter named Recette so this one is a new one, and you can see that my column Running Average doesn't change, the value of the Week 11 is the same as the last table. For this case, I didn't want to calculate the first two weeks and for the W 13, the result should be (60,63+60,00+60,12)=60,25 and not 60,20.

 

To calculate the running average, I summarize my data table to get the table I showed you in the last post and I created the column Index and a column Running Average. To calculate the running average, I used this DAX expression :

 

Running Average = IF('Table'[Index]=1 || 'Table'[Index]=2;0;('Table'[Average]+LOOKUPVALUE('Table'[Average];'Table'[Index];'Table'[Index]-1)+LOOKUPVALUE('Table'[Average];'Table''[Index];'Table'[Index]-2))/3)

 

 

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    9 years ago

    Hi JonathanJohns,

     

    In your scenario, you can create calculated columns below:

     

    WeekNum = VALUE(RIGHT('Table1'[Week],2))
    Rank = RANKX(
         FILTER(
         ALLSELECTED(Table1),Table1[Recette]=EARLIER(Table1[Recette])
                   ),
         'Table1'[WeekNum],
     ,1)
    RunningTotal = CALCULATE(SUM('Table1'[Average]),ALLEXCEPT(Table1,'Table1'[Recette]),'Table1'[Rank]<=EARLIER(Table1[Rank]))
    LastVal = var l=LOOKUPVALUE(Table1[RunningTotal],'Table1'[Index Recette],'Table1'[Index Recette],'Table1'[Rank],'Table1'[Rank]-3)
return 

IF('Table1'[Rank]>3,('Table1'[RunningTotal]-l)/3,BLANK())

     

    Measure = IF(MAX('Table1'[Rank])<3,0,IF(MAX('Table1'[Rank])=3,CALCULATE(AVERAGE(Table1[Average]),FILTER(ALL(Table1),'Table1'[Recette]<=MAX('Table1'[Recette])),'Table1'[Rank]<=3),MAX('Table1'[LastVal])))

     

     

     

    Best Regards,
    Qiuyun Yu

  • JonathanJohns's avatar
    JonathanJohns
    Helper III
    9 years ago

    Thank you so much that was exactly what I wanted to do ! :)