Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
Solved! Go to Solution.
@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])))
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:
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:
@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:
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.
User | Count |
---|---|
76 | |
75 | |
46 | |
31 | |
28 |
User | Count |
---|---|
99 | |
91 | |
51 | |
49 | |
46 |