Forum Discussion
Getting a yearly average from a measure
I have a ratio measure, and would like to take the yearly average of that measure. Here is a screenshot of what I would like to achieve for the new average measure, Measure 2: Measure 1 is ca...
Anonymous ,
Measure 2 = calculate(AvergaeX(Table[Measure1]), filter(allselected(Table),Table[Year]=max(Table[Year])))
or
Measure 2 = calculate(AvergaeX(Table[Measure1]), filter(all(Table),Table[Year]=max(Table[Year])))- Hi Anonymous ,
According to my understanding ,you want to calculate average based on measure and different year,right?I did it in two ways.1.Use Average
Divide column = DIVIDE ( 'average table'[column1], 'average table'[Column2], 0 )average measure = CALCULATE ( AVERAGE ( 'average table'[Divide column] ), ALLEXCEPT ( 'average table', 'average table'[Year] ) )2.Use Averagex
Measure 7 = SUM ( 'average table'[Divide column] )use avaragex = AVERAGEX ( ALLEXCEPT ( 'average table', 'average table'[Year] ), [Measure 7] )
My visualization looks like this:Could my methods help you solve the problem? Hope to give me a reply,thanks.Best Regards,
Eyelyn Qin
Anonymous ,
Measure 2 = calculate(AvergaeX(Table[Measure1]), filter(allselected(Table),Table[Year]=max(Table[Year])))
or
Measure 2 = calculate(AvergaeX(Table[Measure1]), filter(all(Table),Table[Year]=max(Table[Year])))
amitchandak , I used your formula and changed the averagex part to this:
AVERAGEX(SUMMARIZE('Calendar','Calendar'[Date],"Avg",[Measure1]),[Avg])
and then it worked, thanks!
and then it worked, thanks!