Forum Discussion
Helper IVCalculating average of measures
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.
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]))
18 Replies
SamLesterMicrosoft Employee
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)
aktripathi2506Hi,
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.
jahidaImpactful Individual
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].
- v-haibl-msft
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.
- jahida
Have you tried either of the methods we suggested?