Average Issues

[floppysock]

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! 

[FreemanZ · Accepted solution]

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:

[floppysock]

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.