Forum Discussion
Calculating average for measure
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.
6 Replies
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 :)
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
