Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aktripathi2506
Helper IV
Helper IV

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
1 ACCEPTED SOLUTION
v-micsh-msft
Employee
Employee

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:

12.PNG

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

13.PNG

 

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

 

Regards

View solution in original post

1 REPLY 1
v-micsh-msft
Employee
Employee

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:

12.PNG

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

13.PNG

 

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

 

Regards

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.