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
Up please :)
You are going to need to specify a bit more clearly what you need. Most of us will be ... uncomfortable... that you want an average of averages, but regardless... do you always want the most recent *3* values averaged together? Or all of them within 1 Recette?
Best to give us some "sample results"
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.