Forum Discussion
Helper IVCalculating average of measures
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]))
Helper IVThank 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.
Community Champion- 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
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]