Dividing Total time by specific task

[e0145046]

TASK_WORK_MINS   (DURATION)

COMPETE_TASK_COUNT  (0'S AND 1'S)

HIERARCHY LEVEL (3 , 4, 5)

Our company has task that are complete and ones that are sent back. Both types have a duration for them but when I divide the duration by the task complete. I do not get the added time for the ones sent back. 

In excel all of the minutes spend on any task is attached the the employee, however, power bi is only using the completed task instead of adding both ticket types and dividing the compeled ones. 

Example: Tech A has 5 tickets in a week for 1 hour a piece. Tech only completes 3 of them. I want to divide 5 hours by 3 task compelte. In the image below 0's are sent back and 1's are completed. 

[Anonymous]

Hi @e0145046 ,

Were you trying to calculate total hours of completed tasks as a percentage of total hours of all tasks for each employee? 

Try creating the following measures:

1. Measure 1 = SUMX(FILTER(Table, Table[CompleteTaskCount]=1), Table[TaskWorkMins])

2. Measure 2 =
VAR TotalTaskDuration = SUMX(Table, Table[TaskWorkMins])

RETURN

DIVIDE([Measure 1], TotalTaskDuration)

I left the duration in minutes, if you need them in hours just divide by 60.

Hope this helps. 

[e0145046]

@Anonymous

I am wanting to take the SUM of TASK_WORK_MINS for all rows and divide them by the SUM of COMPETE_TASK_COUNT =1

We want to capture the total minutes for each employee regardless if they have closed a task or not. Some of these will have 50 minutes with 0 task and I cannot do a simple divide in PowerBI like I do in Excel. 

[Anonymous · Accepted solution]

Hi @e0145046 ,

The Measure 1 above will achieve the effect of sum of TASK_WORK_MINS for all COMPLETE_TASK_COUNT=1, i.e. total minutes taken for employee for closed tasks. 

In Measure 2, the formula in the variable TotalTaskDuration sums TASK_WORK_MINS regardless of whether task is closed or not (i.e. sum for all rows). As you're looking to divide sum of TASK_WORK_MINS by sum of completed tasks (Measure 1), I have adjusted the formula below:

VAR TotalTaskDuration = SUMX(Table, Table[TaskWorkMins])

RETURN

DIVIDE(TotalTaskDuration, [Measure 1])

I've recreated a small dataset based on the info you provided to test the above measures in a matrix and they are working for me when I have employee in rows. Please give this a try and see if it works. Otherwise, it would be helpful if you could please share the results of the measures and how your intended result should look like.