Calculating average for measure

[aktripathi2506]

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.

[Vvelarde]

hi @aktripathi2506

AverageTotal = AverageX(Summarize('Table';Table[Group ];"MyAverage";[Measure3]);[MyAverage])

[aktripathi2506]

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.

[CheenuSing]

@aktripathi2506

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

[aktripathi2506]

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%

[Vvelarde]

@aktripathi2506

Hi; Try changing Measure3 to:

Measure 3 = AVERAGEX('Table';1-[Measure 1])/ [Measure 2])

And use the Dax that i wrote before

[OwenAuger]

@aktripathi2506

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 🙂