calculate 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

This is the table result of power bi desktop. 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 advise and help.

[v-sihou-msft · Accepted solution]

@aktripathi2506

In this scenario, you should use calculated column rather than a measure. Then you just need to set the aggregate type into Average for that calculated column. See my sample below:

Regards,