cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted

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
Highlighted
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.
Highlighted

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

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

Highlighted

@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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors