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

 

 

 

 

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

4 Replies

  • 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