Calculated table with summarized working time

[IvanS]

Hi guys,

I have received request to calculate working efficiency which part includes consolidation of working time.

Original table with tasks with example of 1 tasks look like below:

Task ID	Created Date	Due Date	Close Date	Task Owner	Team Composition	Total working time (minutes)
123456	25/10/2022	30/10/2022	29/10/2022	User 1 name	User 2 name, User 3 name, User 4 name (etc.)	500
987654	1/11/2022	2/11/2022	2/11/2022	User 4 name		70
456456	14/11/2022	30/11/2022		User 2 name		20

Now, I would need to split "Total working time" equally to Task Owner and users stated in "Team Composition" column. This column have names separated by comma and can have from 0 up to infinite number of users. Also note close time - if this value is blank, the working time should not be calculated as the task is not finished yet.

The result should be overview of each users and the working time spent on tasks. Date refer to close date of task - example below: 

Date	Name	Time of work
25/10/2022	User 1 name	125
25/10/2022	User 2 name	125
25/10/2022	User 3 name	125
25/10/2022	User 4 name	125
2/11/2022	User 4 name	70

Any help is much appreciated!

Thank you

IvanS

[v-jingzhang · Accepted solution]

Hi @IvanS 

Do you must use DAX to split the "Team Composition" column? If so, you may refer to this article: Split a Delimited Row into Multiple Rows using DAX Queries – Some Random Thoughts (sqljason.com)

Otherwise I would prefer to use Power Query as it would be easier. Here is what I do in Power Query Editor:

First split "Team Composition" column by delimiter comma into Rows. 

Perform "Trim" on the new "Team Composition" column to remove any additional leading or ending spaces. 

Apply the change to Power BI Desktop, then create a calculated table with below DAX.

Table 2 = 
VAR _table = FILTER('Table','Table'[Close Date]>BLANK())
RETURN
FILTER(
    DISTINCT(    
        UNION(
            SELECTCOLUMNS(_table,"Task ID",'Table'[Task ID],"Close Date",'Table'[Close Date],"Name",'Table'[Task Owner]),
            SELECTCOLUMNS(_table,"Task ID",'Table'[Task ID],"Close Date",'Table'[Close Date],"Name",'Table'[Team Composition])
        )
    ),
    [Name] <> BLANK()
)

Add a calculated column into above 'Table 2'.

Time of work = 
VAR _memberCount = COUNTROWS(FILTER('Table 2','Table 2'[Task ID] = EARLIER('Table 2'[Task ID])))
VAR _totalMinutes = CALCULATE(MAX('Table'[Total working time (minutes)]),'Table'[Task ID] = EARLIER('Table 2'[Task ID]))
RETURN
DIVIDE(_totalMinutes,_memberCount)

I have attached the sample file. Hope it helps. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[IvanS]

This worked like a charm! Thank you 🙂

[ddpl]

@IvanS ,

Make below operations in Power Query...

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY69CoAwDIRfRTK3kKR/9kGcSgeHjjro+4Ox1WJByPDluDsuJSA21nlQwE4TamZBgx05dlzOckw07etW3o/rpyqbD9vX5RAhqwRxDt5ZEZA0UetD/sMxLxdag4xsO8l2773zwXFRE1iS+QI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Task ID" = _t, #"Created Date" = _t, #"Due Date" = _t, #"Close Date" = _t, #"Task Owner" = _t, #"Team Composition" = _t, #"Total working time (minutes)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Task ID", Int64.Type}, {"Created Date", type text}, {"Due Date", type text}, {"Close Date", type text}, {"Task Owner", type text}, {"Team Composition", type text}, {"Total working time (minutes)", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Team", each if [Team Composition] <> "" then [Task Owner] & "," & [Team Composition] else [Task Owner], type text),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Team", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Team.1", "Team.2", "Team.3", "Team.4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Team.1", type text}, {"Team.2", type text}, {"Team.3", type text}, {"Team.4", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Task ID", "Created Date", "Due Date", "Close Date", "Task Owner", "Team Composition", "Total working time (minutes)"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
#"Removed Columns"

After that create a calculated column in DAX as shown below...

Time of Work =
IF(
    'Table'[Close Date] <> BLANK(),
    DIVIDE(
        'Table'[Total working time (minutes)],
        COUNTX(FILTER('Table', 'Table'[Task ID] = EARLIER('Table'[Task ID])), 'Table'[Task ID])
    )
)