Forum Discussion
Average Issues
Hi all - I'm struggling to figure out how to get the averages working correctly for a data set that already has averages in.
The data set - e.g. row 1 is saying Agent 1 completed 3 tasks and they averaged 3 hours each to complete (i.e. 4 hours, 3 hours and 2 hours to complete each task averaging 3 hours):
| Team Name | Agent ID | Volume Completed | AVG Hours to Complete |
| Team 1 | Agent 1 | 3 | 3 |
| Team 1 | Agent 2 | 5 | 7 |
| Team 1 | Agent 3 | 8 | 5 |
| Team 1 | Agent 4 | 4 | 7 |
| Team 2 | Agent 5 | 3 | 3 |
| Team 2 | Agent 6 | 5 | 2 |
| Team 2 | Agent 7 | 6 | 2 |
| Team 2 | Agent 8 | 8 | 3 |
I need to create a visualisation table which will show the following, I have it all apart from the final column:
| Team Name | Agent Volume | Volume Completed | AVG Hours to Complete |
| Team 1 | 4 | 20 | ? |
| Team 2 | 4 | 22 | ? |
I know that the avg hours to complete per team will be something along the lines of:
SUM(Volume Completed * AVG Hours to Complete) / Total Volume Completed by team
But I don't know how to get this to work in PowerBI.
Any help would be really appreciated!
hi floppysock
try to plot a table visual wit the Team Name column and a measure like:
Avg = DIVIDE( SUMX( TableName, TableName[Volume Completed]*TableName[AVG Hours to Complete] ), SUM(TableName[Volume Completed]) )it worked like:
2 Replies
hi floppysock
try to plot a table visual wit the Team Name column and a measure like:
Avg = DIVIDE( SUMX( TableName, TableName[Volume Completed]*TableName[AVG Hours to Complete] ), SUM(TableName[Volume Completed]) )it worked like:
This is exactly what I needed - thanks so much.
I think I need to go read up and explore the SUMX etc functions as the key lies there.
