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