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

JonathanJohns's avatar
JonathanJohns
Helper III
9 years ago

With this DAX expression, I don't use the column Recette so I can't get the last table. Use a filter with the column Week is good but how do for the colum Recette because this one is a text column. That is my problem ^^ I know how to get a table like this one :

 

      Week                Average              Index    Running Average

0017-W0960,374171428571410
0017-W1060,322105263157920
0017-W1160,2242794759825360,306852055904
0017-W1260,265670995671460,2706852449371
0017-W1360,0951351351351560,1950285355962
0017-W1459,8387096774194660,0665052694085
0017-W1560,2029441624365760,045596324997
0017-W1660,3698275862069860,1371604753543
0017-W1760,2329545454545960,268575431366

 

I would like just to use a second filter with the column Recette now because this table doesn't separate by Recette.

 

 I hope you know what I mean. :)

 

Thank you.

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 ! :)