Forum Discussion

rush's avatar
rush
Helper V
8 years ago
Solved

Calculated Column that totals Hours by User, Task

Hi All   Is it possible to group the totals in a column so that it sums up all the values from one column into a new one by month and year, user & on a task without having to create a new table to ...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi rush,

     

    Add an index column in Query Editor mode.

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Year-Month", "WorkCode", "User"}, {{"All rows", each Table.AddIndexColumn(_, "Index",1,1), type table}}),

     

     

    After applying above changes, remember to set the data type for [Total Hours] to whole number. Then, modify the DAX formula as:

    Bench Test =
VAR Maxindex =
    CALCULATE (
        MAX ( Billing_Info[Index] ),
        ALLEXCEPT (
            Billing_Info,
            Billing_Info[Year-Month],
            Billing_Info[WorkCode],
            Billing_Info[User]
        )
    )
RETURN
    IF (
        Billing_Info[Index] = Maxindex,
        CALCULATE (
            SUM ( Billing_Info[Total Hours] ),
            ALLEXCEPT (
                Billing_Info,
                Billing_Info[User],
                Billing_Info[Year-Month],
                Billing_Info[WorkCode]
            ),
            Billing_Info[WorkCode] IN { "Bench" }
        ),
        BLANK ()
    )

     

    Best regards,

    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi rush,

 

Please try:

TotalHoursBench =
CALCULATE (
    SUM ( Tablename[Total Hours] ),
    ALLEXCEPT ( Tablename, Tablename[User], Tablename[Yesr-Month] )
)

Regards,

Yuliana Gu

  • rush's avatar
    rush
    Helper V
    8 years ago

    Thanks v-yulgu-msft

    It almost works but brings back duplicated total hours for each row rather than bringing back 1 total value per year-month, per user, per workcode.

     

    Bench Test =
    CALCULATE (
     SUM ( Billing_Info[TotalHours] ),
     ALLEXCEPT (
     Billing_Info,
     Billing_Info[UserID],
     Dim_Date[MonthName_Year],
     Billing_Info[WorkCode]
     ),
     Billing_Info[WorkCode] IN { "Bench" }
    )
     
    • v-yulgu-msft's avatar
      v-yulgu-msft
      Microsoft Employee
      8 years ago

      Hi rush,

       

      Add an index column in Query Editor mode.

      #"Grouped Rows" = Table.Group(#"Changed Type", {"Year-Month", "WorkCode", "User"}, {{"All rows", each Table.AddIndexColumn(_, "Index",1,1), type table}}),

       

       

      After applying above changes, remember to set the data type for [Total Hours] to whole number. Then, modify the DAX formula as:

      Bench Test =
VAR Maxindex =
    CALCULATE (
        MAX ( Billing_Info[Index] ),
        ALLEXCEPT (
            Billing_Info,
            Billing_Info[Year-Month],
            Billing_Info[WorkCode],
            Billing_Info[User]
        )
    )
RETURN
    IF (
        Billing_Info[Index] = Maxindex,
        CALCULATE (
            SUM ( Billing_Info[Total Hours] ),
            ALLEXCEPT (
                Billing_Info,
                Billing_Info[User],
                Billing_Info[Year-Month],
                Billing_Info[WorkCode]
            ),
            Billing_Info[WorkCode] IN { "Bench" }
        ),
        BLANK ()
    )

       

      Best regards,

      Yuliana Gu

      • rush's avatar
        rush
        Helper V
        8 years ago

        v-yulgu-msft Thank you for your response. Much appreciated.

        I cannot seem to get the Index to appear in my query:

        My current query below:

        let
    Source = Table.Combine({#"Mar 2017", #"Apr 2017", #"May 2017", #"Jun 2017", #"Jul 2017", #"Aug 2017", #"Sep 2017", #"Oct 2017", #"Nov 2017", #"Dec 2017", #"Jan 2018", #"Feb 2018", #"Mar 2018", #"Apr 2018", #"May 2018", #"Jun 2018", #"Jul 2018", #"Aug 2018", #"Sep 2018", #"Oct 2018", #"Nov 2018", #"Dec 2018", #"Jan 2019", #"Feb 2019"}),
    #"Filtered Rows" = Table.SelectRows(Source, each ([UserName] <> null and [UserName] <> "Admin Admin")),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","(Pty) Ltd","",Replacer.ReplaceText,{"ClientName"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Pty","",Replacer.ReplaceText,{"ClientName"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","(PTY) LTD","",Replacer.ReplaceText,{"ClientName"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Ltd","",Replacer.ReplaceText,{"ClientName"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","(PTY)","",Replacer.ReplaceText,{"ClientName"}),
    #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","PTY LTD","",Replacer.ReplaceText,{"ClientName"}),
    #"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","Limited","",Replacer.ReplaceText,{"ClientName"}),
    #"Filtered Rows1" = Table.SelectRows(#"Replaced Value6", each [UserID] <> 2024),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows1", "YYYY_MM", each Date.ToText([Date], "yyyy-MM")),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"YYYY_MM", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"YYYY_MM", "WorkCode", "UserName"}, {{"All rows", each Table.AddIndexColumn(_, "Index",1,1), type table}})

in
    #"Changed Type"