Forum Discussion
Calculating average for measure
Hi Vvelarde,
I was using the similar formula before but not getting the result.
I was using this equation:
_M_New2 = AVERAGEX(SUMMARIZE(Table,Table[Group],"toAverage",[Measure 3]),[Measure 3])
answer I was getting : 0.72 (72%)
This I changed to
_M_New2 = AVERAGEX(SUMMARIZE(Table,Table[Group],"toAverage",[Measure 3]),[toAverage])
and now answer I am getting is: 0.53 (53%)
Both are not giving the correct expected answer.
Thank you.
If you follow OwenAuger approach it will give you the desired result.
Only thing you need to do is change YourTable[Group] by YourTable
Measure 3 =
AVERAGEX (
YourTable,
1 - DIVIDE ( [Measure 1], [Measure 2] )
)
If this is what are you want please accept OwenAuger reply as a solution and KUDOS to me and OwenAuger
Cheers
CheenuSing
Hi Anonymous
Thank you for replying.
Measure 1 , measure 2 are not just column name they are acually measure calculate by other columns.
What I want to calculate is the average of [1-(measure1/measure2)]
Measure 1 is : SUM(table1[Hours Spent]) (after applying some page level filter)
Measure 2 is: total hour spent by the group by ignoring any filter/slicer except few which I have done by using the filter allexcept.
Measure 3 is simply : 1- DIVIDE([Measure 1],[Measure 2])
This is what I got by using the formula you suggested. When I selected another week from the week slicer. Again the result is not matching with the expected output.
Group Measure 1 Measure 2 Measure 3 New Measure A 51 100.00% B 9 659.75 98.60% 100% C 20.5 775.75 97.40% 100% D 1 100.00% E 100.00% F 2.5 731.75 99.70% 50% G 13.5 61.5 78.00% 71% H 100.00% I 100.00% J 46 239 80.80% 79% K 100.00% L 100.00% M 4.5 411.75 98.90% 94% N 7 428.75 98.40% 100% O 174.75 100.00% P 12.5 432.25 97.10% 99% Q 1.5 56 97.30% 0% R 14.75 224.75 93.40% 79% S 100.00% Total 131.75 4248 96.90% 84% Hi; Try changing Measure3 to:
Measure 3 = AVERAGEX('Table';1-[Measure 1])/ [Measure 2])
And use the Dax that i wrote before
