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]))
Microsoft Employee
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
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.
- jahida
Impactful Individual
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.
- Vvelarde
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]
