Calculated Column that totals Hours by User, Task

[rush]

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 achieve the outcome?:

[v-yulgu-msft]

Hi @rush,

Please try:

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

Regards,

Yuliana Gu

[rush]

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 · Accepted solution]

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]

@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"