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 ...
  • 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.
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.