Forum Discussion
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[Index]=1 || NewTable[Index]=2;0;(NewTable[Average]+LOOKUPVALUE(NewTable[Average];NewTable[Index];NewTable[Index]-1)+LOOKUPVALUE(NewTable[Average];NewTable[Index];NewTable[Index]-2))/3)
But know I get this table :
| Recette | Week | Average | Index |
| 12 | 0017-W06 | 59,92 | 2 |
| 12 | 0017-W07 | 60,4125 | 3 |
| 12 | 0017-W08 | 60,24 | 4 |
| 12 | 0017-W09 | 59,74666667 | 5 |
| 12 | 0017-W11 | 60,63 | 7 |
| 12 | 0017-W12 | 59,99666667 | 8 |
| 12 | 0017-W13 | 60,124 | 9 |
| 13 | 0017-W05 | 59,93193548 | 1 |
| 13 | 0017-W06 | 60,1856701 | 2 |
| 13 | 0017-W07 | 60,0341573 | 3 |
| 13 | 0017-W08 | 59,94153061 | 4 |
| 13 | 0017-W09 | 60,13837209 | 5 |
| 13 | 0017-W10 | 59,91090909 | 6 |
| 13 | 0017-W11 | 59,988125 | 7 |
| 13 | 0017-W12 | 60,09785047 | 8 |
| 13 | 0017-W13 | 59,94844444 | 9 |
| 14 | 0017-W05 | 62,23333333 | 1 |
| 14 | 0017-W06 | 62,16083333 | 2 |
| 14 | 0017-W09 | 62,09888889 | 5 |
| 14 | 0017-W13 | 61,843 | 9 |
I would like to do the same calculation but for each Recette in my column Recette. Do you know how to do please ?
Thank you for your help.
Jonathan
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
9 Replies
Up please :)
Sorry if I was not enough clear...
I would like to get a table like this one :
Recette Week Average Index Week Index Recette Running Average 10 0017-W15 60,1529464 7 1 0 10 0017-W16 60,2357426 8 1 0 10 0017-W17 59,9529323 9 1 60,1138738 12 0017-W11 60,63 3 2 0 12 0017-W12 59,9966667 4 2 0 12 0017-W13 60,124 5 2 60,2502222 13 0017-W09 60,2085227 1 3 0 13 0017-W10 59,9109091 2 3 0 13 0017-W11 59,988125 3 3 60,0358523 13 0017-W12 60,0978505 4 3 59,9989615 13 0017-W13 59,88 5 3 59,9886585 13 0017-W14 59,6811765 6 3 59,8863423 I know how to calculate the two columns "Index". But I don't know how to make the DAX expression to calculate my running average to get this table.
For example for the recette 10, because I want to calculate a running average from the three last weeks, I put a 0 for the two first weeks and I do this calculation for the third week : (60,1529464+60,2357426+59,9529323)=60,1138738. This value is my running average from the last three weeks.
That is the same calculation for the recette 12.
For the recette 13, that is the same calculation at the beginning but from the Week 12, the calculation is : (Average W10+Average W11 + Average W12)/3 = 59,9989615
And for the Week 13, the calculation is : (Average W11 + Average W12 + Average W13)=59,9886585
I hope you see what I mean.
Thank you for your help.
Jonathan
