Forum Discussion

IvanS's avatar
IvanS
Helper V
3 years ago
Solved

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...
Need Help
Tips and Tricks
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    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.

ddpl's avatar
ddpl
Solution Sage
3 years ago

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])
    )
)