Running Average Calculation

[JonathanJohns]

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

[JonathanJohns]

Up please 🙂

[Anonymous]

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"

[JonathanJohns]

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

[Anonymous]

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.

[JonathanJohns]

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.

[Anonymous]

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]

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 · Accepted solution]

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]

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