cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper IV
Helper IV

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

 

Cal Column.PNG

 

 

 

1 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}}),

1.PNG

 

 

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

2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Microsoft
Microsoft

Hi @rush,

 

Please try:

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

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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" }
)
 

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}}),

1.PNG

 

 

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

2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors