Calculating average of measures

[aktripathi2506]

I have created a measure which calculate the efficiency per week by following formula:

1 - DIVIDE([Hour Spent],[Total available hours])

I have calculated week after week this, now I want the average of these values (13 week i.e. quarter 1) as a result.

week	group	Hours Spent	Average of Capacity	Efficiency
1	sales		214	1
2	sales	4.5	552.5	0.991855
3	sales	10	712	0.985955
4	sales	10.5	659.5	0.984079
5	sales	20.5	734	0.972071
6	sales	25	776.75	0.967815
7	sales	32	622.5	0.948594
8	sales	25	493.75	0.949367
9	sales	4	653.25	0.993877
10	sales	11.5	715	0.983916
11	sales	9.5	1051.25	0.990963
12	sales	6.5	667.5	0.990262
13	sales	21.5	627.75	0.965751
Total		180.5	690.97	0.74

As we can see at the bottom I am getting .74 , rather than this I am expecting the average of efficiency which should be .9788.

How can we get that value. Please advice and help.

[v-haibl-msft]

@aktripathi2506,

You can try to use AVERAGEX(TableName, [Measure]) as @jahida mentioned. Another method is to change your Efficiency from measure to column, so that you can use the Quick Calc function (Average) in the Table chart.

If problem still persists, please provide some sample data of your tables so that we can solve it exactly.

Best Regards,

Herbert

[aktripathi2506]

Thank you @v-haibl-msftand @jahida

Please find below the example of raw data:

week	hours spent	Available hours for the week
1	2	200
1	4	200
1	6	200
1	5	200
1	6	200
1	3	200
1	6	200
1	8	200
1	9	200
1	4	200
2	2	400
2	8	400
2	1	400
2	5	400
2	0	400
2	3	400
3	7	500
3	4	500
3	9	500
3	3	500
3	1	500
4	5	100
4	3	100
4	1	100
4	7	100
5	3	600
5	9	600
5	2	600
5	6	600
5	4	600

This is the raw data.

The desired output is as below calculated in excel:

Week	Sum of Hours	average of available hours	efficiency	% eff
1	53	200	0.265	74%
2	19	400	0.0475	95%
3	24	500	0.048	95%
4	16	100	0.16	84%
5	24	600	0.04	96%
		Final exp. Result	0.11	89%

Now I am trying to do the same thing in power BI with the very large data set for 52 weeks.

Please advise.

[Vvelarde]

hi @aktripathi2506

This is the result that you want?

Efficiency per Week and Total

[aktripathi2506]

Hi @Vvelarde,

Seems good. Can you please advise how did you do that so I can also try the same and then confirm you if it works for me too.

Thanks.

[Vvelarde]

hi @aktripathi2506

Measure Efficiency:

Efficiency =
AVERAGEX (
    SUMMARIZE (
        'Average-Table';
        'Average-Table'[week];
        "Efficiency"; SUM ( 'Average-Table'[hours spent] )
            / AVERAGE ( 'Average-Table'[Available hours for the week] )
    );
    [Efficiency]
)

Measure%Effic:

%Effic = 1-[Efficiency]

[jahida]

Have you tried either of the methods we suggested?

[aktripathi2506]

Yes I tried your its not giving me the expected result. I thought about creating the new column before as well but it is difficult because: you have to get the sum of hours grouping by week and then divide it by the average of available hours group by week.

In a new column i can not do  [hours spent/available hours for each week] for each row because that will give me different value.

[jahida]

What do you have in PowerBI right now? Do you only have one table or have you built out that summary table at all yet? I think both of us assumed you had that summary table built out, which might be incorrect.

[aktripathi2506]

In power BI I have the first table in my previous post refered as raw data.

That summary table I have given is just for understanding point of view (Excel way). 

I want to do all this calculation in power BI, in excel it is easy to do, thats why I gave example by excel that how to achieve the desired result.

[jahida]

Yeah ok my bad for misunderstanding. Did you try the most recent measure?

[aktripathi2506]

Hi,

What you mean by most recent measure? I tried the one you suggested.

Thanks.

[jahida]

Message 10 in the thread, I've pasted it here:

AVERAGEX(SUMMARIZE(Table, Table[week], "Sum of Hours", SUM(Table[hours spent]), "Average available", AVERAGE(Table[Available hours for the week])), 1 - DIVIDE([Sum of Hours], [Average available]))

[jahida · Accepted solution]

Asumming you don't have that summary table built out and you just want to plug the initial table into a matrix or table visual, here's a measure you can use (might be minor syntax errors, I'm not testing but I'm pretty sure the concept is sound):

AVERAGEX(SUMMARIZE(Table, Table[week], "Sum of Hours", SUM(Table[hours spent]), "Average available", AVERAGE(Table[Available hours for the week])), 1 - DIVIDE([Sum of Hours], [Average available]))

[aktripathi2506]

Hi @jahida @Vvelarde,

by using this formula I created the measure which calculates the average of 13 weeks i.e. 1 quarter, for different group.

Please find below the table which I got as an output.

Group Name	Measure
Sales	87.86%
Marketing	98.98%
manuf.	97.57%
IT	99.86%
DB	100.00%
SSC	98.75%
HR
Comm	94.13%
AB	96.48%
DT	97.76%
Ramp	99.25%
Control	99.13%
planning	95.08%
Distr.	97.64%

This is working fine, Now I am strugling to add one more addition in it..

How can we get the average of these numbers displaying in power bi table/matrix vidual?

Average

97.11%

This is what I want to add.

Thanks.

[aktripathi2506]

Awesome. This is great.

Very good logic, solved the problem.

Thanks a lot truely.

[SamLester]

The .74 you are seeing is the calculation of 1 - DIVIDE([Hour Spent],[Total available hours]) (1 - 180.5/690.97) for the totals, not the average of the efficiencies measures. My suggestion would be to remove the Totals line and calculate a new measure that is the average of the efficiencies as opposed to applying the efficiencies formula to the totals, which is what you are currently doing.

Thanks,
Sam Lester (MSFT)

[aktripathi2506]

Hi,

Thank you for the reply. Yes .74 is becoz of  (1 - 180.5/690.97) . 

But its not possible to create a measure which is average of another measure, it should be column as average function only accepts a column reference as an argument. Thats why its not working in my case.

[jahida]

I need a bit more information about the structure of your data to give an exact answer, but in general, the simple work-around to the AVERAGE function requiring a column is to use AVERAGEX(TableName, [Measure]). So something like AVERAGEX(Table, ___) might work, where ___ is either [Efficiency] or the DAX expression used to generate [Efficiency].