Forum Discussion

aktripathi2506's avatar
aktripathi2506
Icon for Helper IV rankHelper IV
9 years ago
Solved

Calculated column by using group by other columns.

Hi All,

 

I want to calculate total hour spent by the employee for a day

 

Please find below the example

I have also included the desired column which I am trying to get.

With this I want to calculate the employee who spent more than 8 hours and who spent less than 8 hours in a day.

 

NameDateTaskHours spent Expected Output column
Raj20-09-20161112 6
Raj20-09-20161124 6
Raj21-09-20161131 1
Raj22-09-20161146 10
Raj22-09-20161154 10
App20-09-20161163 6
App20-09-20161172 6
App20-09-20161181 6
App22-09-20161194 15
App22-09-20161206 15
App22-09-20161215 15
Pom20-09-20161228 16
Pom20-09-20161234 16
Pom20-09-20161241 16
Pom20-09-20161253 16
Pom21-09-20161261.5 1.5
Pom22-09-20161273.5 3.5
Ken23-09-20161282 9
Ken23-09-20161291.5 9
Ken23-09-20161302.5 9
Ken23-09-20161313 9
Need Help
  • v-micsh-msft's avatar
    v-micsh-msft
    9 years ago

    Hi aktripathi2506,

     

    Please take a try with the formula below when creating the calculated column:

    Result = sumx(

                       filter(

                          'Table 0',

                          And('Table 0'[Name]=earlier('Table 0'[Name]),

                                  'Table 0'[Date]=earlier('Table 0'[Date]))),

                       'Table 0'[Hours spent])

    See my testing result:

    For Users whose total hours is less than 8, take use of slicer.

     

    If any further help needed, please feel free to post back.

     

    Regards

1 Reply

  • v-micsh-msft's avatar
    v-micsh-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi aktripathi2506,

     

    Please take a try with the formula below when creating the calculated column:

    Result = sumx(

                       filter(

                          'Table 0',

                          And('Table 0'[Name]=earlier('Table 0'[Name]),

                                  'Table 0'[Date]=earlier('Table 0'[Date]))),

                       'Table 0'[Hours spent])

    See my testing result:

    For Users whose total hours is less than 8, take use of slicer.

     

    If any further help needed, please feel free to post back.

     

    Regards