Distribute % of Time

[buchabl]

I have a table with columns showing:

1) users: the name of the person performing the task

2) tasks: a desciption of what a specific user is doing

3) task numbers: a number (1-21) given to the task so that you can identify the task without having a long description

4) actions: a description of an action being done by a user; multiple actions can make up a single task or a single task can consist of only one action

5) action identifiers: a special name given to each action in each task so that you can identify the action without having a long description

6) times: the amount of time a user spent on an action (in minutes)

There should not be any duplicates of action identifiers because each task consists of actions unique to that task; However, it was discovered that one action could apply to two other tasks (3 tasks total with the same action). This means we would have 3 instances of the action so there would be duplicates of the action identifier. 

Let's say the action showed up for tasks 5, 9, and 10. What I did was put the time associated with that action to just one task, let's say it was task number 5. Once it was assigned to the single task (task 5), I wanted to distribute the time outside of query editory.

1) 50% to the original task that had 100% of the time (task 5)

2) 25% to the second task that would use this action (task 9)

3) 25% to the second task that would use this action (task 10)

I wasn't sure how to do this so I was hoping I could get some assistance.

[tamerj1]

@buchabl 
Here is a sample file with the solution https://www.dropbox.com/t/hL6nwfkufCgQryUU

In order to re-add the deleted columns we need to genereate a new table as follows

Full Data = 
VAR Task1Table =
    FILTER ('Data', 'Data'[Task #] = 1 && 'Data'[Action Identifier] = "VacuumFloor" )
VAR SelectedData =
    SELECTCOLUMNS ( Task1Table, "Employee", [Employee], "Original Time", [Time (Min)] )
VAR Task1ToAdd =
    ADDCOLUMNS ( SelectedData, "Task #", 1, "Task", "Build a house", "Action", "*Some long description of what action is being performed*", "Action Identifier", "VacuumFloor", "Time (Min)", [Original Time]/2 )
VAR Task2ToAdd =
    ADDCOLUMNS ( SelectedData, "Task #", 2, "Task", "Clean a car", "Action", "*Some long description of what action is being performed*", "Action Identifier", "VacuumFloor", "Time (Min)", [Original Time]/4 )
VAR Task4ToAdd =
    ADDCOLUMNS ( SelectedData, "Task #", 4, "Task", "Give a hairct", "Action", "*Some long description of what action is being performed*", "Action Identifier", "VacuumFloor", "Time (Min)", [Original Time]/4 )
VAR AllTasksToAdd =
    SELECTCOLUMNS ( UNION ( Task1ToAdd, Task2ToAdd, Task4ToAdd ), "Employee", [Employee], "Task #", [Task #], "Task", [Task], "Action", [Action], "Action Identifier", [Action Identifier], "Time (Min)", [Time (Min)] )
RETURN
    UNION ( EXCEPT ( Data, Task1Table ), AllTasksToAdd )

[tamerj1]

Hi @buchabl 

new column =
VAR ActionTime =
SUMX(CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[action identifier)), Data[Time] )

RETURN

IF ( Data[Time] > 0, 0.5*ActionTime, 0.25*ActionTime)

[buchabl]

I think I am a little confused on how to use this logic so I am providing a table in hopes you can elaborate on what values go where....

I have the action identifier "VacuumFloor" occur in 3 different tasks (1, 2, and 4) so right now all time for VacuumFloor is going to task 1 because we technically cannot have duplicates for action identifiers but I want only 50% of the total VacuumFloor time to go to task 1, then 25% to task 2, and the remaining 25% to task 4. 

[tamerj1]

@buchabl 
Here is a sample file for your reference https://www.dropbox.com/t/8MdXdQ39uvh9SEnj
In order to re-add the removed rows we have to create a new table. However, the modified time column is first added to the original table

Time (Min) Modified = 
VAR ActionTable =
    CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Action Identifier] ) )
VAR ActionTime =
    SUMX ( ActionTable, Data[Time (Min)] )
VAR Result =
    IF (
        Data[Action Identifier] <> "VacuumFloor",
        Data[Time (Min)],
        0.5 * DIVIDE ( ActionTime, COUNTROWS ( ActionTable ) )
    )
RETURN
    Result

As per sample data the total time available in the table for VacuumFloor is 10 + 5 = 15 and this time will be distributed as follows:

• 50% (7.5 min) to task 1 (split in to two records, esch of 3.5min)
• 50% (3.5 min) to task 2 (split in to two records, esch of 1.75min)
• 50% (3.5 min) to task 4 (split in to two records, esch of 1.75min)

Please let me know if above assumption is wrong.

The appended table code is

Full Data = 
VAR VacuumFloorTime =
    CALCULATE ( SUM (Data[Time (Min) Modified] ), Data[Action Identifier] = "VacuumFloor" )
VAR SelectedData =
    SELECTCOLUMNS ( Data, "Employee", Data[Employee], "Task #", Data[Task #], "Task", Data[Task],"Action", Data[Action] )
VAR DistinctTask =
    DISTINCT ( SelectedData )
VAR Task2 =
    FILTER ( DistinctTask, [Task #] = 2 )
VAR Task2ToAdd =
    ADDCOLUMNS (
        Task2,
        "Action Identifier", "VacuumFloor", "Time (Min)", "", "Time (Min) Modified", 0.5 * DIVIDE ( VacuumFloorTime, COUNTROWS ( Task2 ) ) 
    )
VAR Task4 =
    FILTER ( DistinctTask, [Task #] = 4 )
VAR Task4ToAdd =
    ADDCOLUMNS (
        Task4,
        "Action Identifier", "VacuumFloor", "Time (Min)", "", "Time (Min) Modified", 0.5 * DIVIDE ( VacuumFloorTime, COUNTROWS ( Task4 ) ) 
    )
RETURN
    UNION ( Data, Task2ToAdd, Task4ToAdd )

[buchabl]

@tamerj1 The only issue is that I need to distribute the time for each instance it occurs individually for each user so the 10 would be distributed and then the 5 would be distributed seperately; So it would be 5 min to task 1 and 2.5 min to task 2 and 2.5 min to task 4 in the first instance and then distributing the second instance seperately but using the same proportions

[tamerj1]

@buchabl 

This should work only for "VacuumFloor" as the only identifier which has duplicates. If you need something more genersl please let me know. 

new column =
VAR ActionTime =
    CALCULATE ( SUM ( Data[Time] ), ALLEXCEPT ( Data, Data[Action Identifier] ) )
RETURN
    IF (
        Data[Action Identifier] <> "VacuumFloor",
        Data[Time],
        IF ( Data[Time] > 0, 0.5 * ActionTime, 0.25 * ActionTime )
    )

[buchabl]

@tamerj1 

Okay, I think it's starting to make more sense but still isn't quite giving me what I need after testing it with the data I am working with... assume that "VacuumFloor" is part of tasks 1, 2, and 4 as in the picture from my previous reply but the difference is that VacuumFloor is not visibly listed under any of the tasks but task 1. We know that VacuumFloor exists in tasks 2 & 4 but it is not technically there in the table because the duplicates were deleted in PowerQuery. So how would things change if I wanted 25% of the time from VacuumFloor in task 1 to be added to task 2 and task 4 so that VacuumFloor in task 1 would only have 50% of the total time it originally had

[buchabl]

@tamerj1  OR if I wanted to add 25% of the VacuumFloor time in task 1 to an action identifier that is unique to task 2 and then the other 25% to another action identifier that is unique to task 4 so again there would only be 50% of the original total time left under VacuumFloors in task 1

[tamerj1]

@buchabl 

I will look into it and get back to you. 

[tamerj1]

@buchabl 

This is more general. Incase you more than one identifier that has duplicates. 

new column =
VAR ActionTable =
    CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Action Identifier] ) )
VAR TotalActionTime =
    SUMX ( ActionTable, Data[Time] )
VAR MaxActionTime =
    MAXX ( ActionTable, Data[Time] )
RETURN
    IF (
        TotalActionTime <> MaxActionTime && COUNTROWS ( ActionTable ) <> 1,
        Data[Time],
        IF ( Data[Time] > 0, 0.5 * ActionTime, 0.25 * ActionTime )
    )

[buchabl]

@tamerj1 

[collinq]

Hi @buchabl ,

There are some ways to do this in Power Query as well as in DAX.  The DAX method would be more effective and efficient in my mind.  Therefore, if I can figure out how, I am going to move this over to the Developer forum so the individuals there can do this much much quicker than I could.