Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have 3 measures.
Measure 1, Measure 2 and Measure 3.
Measure 3 is getting calculated based on other two measure by using the formula:
Measure 3 = 1 – (Measure 1/ Measure 2)
Now I want to calculate the average of measure 3.
In power BI i am getting it as follows:
Group | Measure 1 | Measure 2 | Measure 3 |
A | 100.00% | ||
B | 64.75 | 100.00% | |
C | 4 | 40 | 90.00% |
D | 1 | 100.00% | |
E | 5 | 100.00% | |
F | 3 | 3 | 0.00% |
G | 100.00% | ||
H | 100.00% | ||
I | 1 | 5.5 | 81.80% |
J | 10 | 100.00% | |
K | 2 | 23.5 | 91.50% |
L | 10.5 | 100.00% | |
M | 36.25 | 100.00% | |
N | 100.00% | ||
O | 13 | 100.00% | |
Total | 10 | 212.5 | 95.30% |
In power BI I am getting 95.3% (1 -10/212.5) but this is not what I want...the expected output is the average of these numbers in measure 3 which should be 90.89%. I want to take the average of group efficency.
Please advise.
AverageTotal = AverageX(Summarize('Table';Table[Group ];"MyAverage";[Measure3]);[MyAverage])
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 @CheenuSing
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
You can use AVERAGEX to calculate the arithmetic mean of an expression across the values of Group.
In your case, you could redefine Measure 3 to be:
Measure 3 = AVERAGEX ( VALUES ( YourTable[Group] ), 1 - DIVIDE ( [Measure 1], [Measure 2] ) )
Or leave Measure 3 as is, and define an additional average measure:
Measure 3 avg =
AVERAGEX ( VALUES ( YourTable[Group] ), [Measure 3] )
Owen 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
105 | |
87 | |
74 | |
66 |
User | Count |
---|---|
124 | |
112 | |
96 | |
82 | |
72 |