Forum Discussion

e0145046's avatar
e0145046
Regular Visitor
5 years ago
Solved

Dividing Total time by specific task

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's avatar
    Anonymous
    5 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      e0145046
      Regular Visitor
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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.