Forum Discussion
Calculated table with summarized working time
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
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.
3 Replies
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.This worked like a charm! Thank you 🙂
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])
)
)
