Forum Discussion

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

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

Running Average Calculation.zip49 KB

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

Running Average Calculation.zip49 KB