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 calculated from 2 columns in my dataset, using DAX: Measure 1 = DIVIDE(Table1[Column1],Table1[Column2],0)
I have tried using this formula for Measure 2:
Thank you for the help!
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
4 Replies
- 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])))Hi amitchandak ,
I cant seem to use that formula. After AverageX, Table[Measure1] does not seem to be an option for the first arguement, only Table is available in the drop down list.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!