Forum Discussion
Running Average Calculation
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
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
I would expect something like:
Avg3 := CALCULATE(AVERAGE(MyTable[Average]),
FILTER(ALL(MyTable),
MyTable[Index Week] >= MAX(MyTable[Index Week] - 2 &&
MyTable[Index Week] <= MAX(MyTable[Index Week])
)
And add back your IF statements.
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-W09 60,3741714285714 1 0 0017-W10 60,3221052631579 2 0 0017-W11 60,2242794759825 3 60,306852055904 0017-W12 60,265670995671 4 60,2706852449371 0017-W13 60,0951351351351 5 60,1950285355962 0017-W14 59,8387096774194 6 60,0665052694085 0017-W15 60,2029441624365 7 60,045596324997 0017-W16 60,3698275862069 8 60,1371604753543 0017-W17 60,2329545454545 9 60,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.
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?
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)