Calculated column by using group by other columns.

[aktripathi2506]

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.

Name	Date	Task	Hours spent		Expected Output column
Raj	20-09-2016	111	2		6
Raj	20-09-2016	112	4		6
Raj	21-09-2016	113	1		1
Raj	22-09-2016	114	6		10
Raj	22-09-2016	115	4		10
App	20-09-2016	116	3		6
App	20-09-2016	117	2		6
App	20-09-2016	118	1		6
App	22-09-2016	119	4		15
App	22-09-2016	120	6		15
App	22-09-2016	121	5		15
Pom	20-09-2016	122	8		16
Pom	20-09-2016	123	4		16
Pom	20-09-2016	124	1		16
Pom	20-09-2016	125	3		16
Pom	21-09-2016	126	1.5		1.5
Pom	22-09-2016	127	3.5		3.5
Ken	23-09-2016	128	2		9
Ken	23-09-2016	129	1.5		9
Ken	23-09-2016	130	2.5		9
Ken	23-09-2016	131	3		9

[v-micsh-msft · Accepted solution]

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