Calculated Column With Department with Highest Sum of Hours

[paul_luvaglia]

Apologies for the slightly confusing thread title but struggling to summarise what I`m trying to do

I have 2 tables.

Table 1 contains 2 columns Employee Name & Date

Employee	Date
Dave	13/02/2022
Dave	14/02/2022
Dave	15/02/2022
Pete	13/02/2022
Pete	14/02/2022
Pete	15/02/2022
Jeff	13/02/2022
Jeff	14/02/2022
Jeff	15/02/2022

Table 2 contains a list of jobs that each employee has booked onto for each date. it includes the Department that the employee worked in for that job

Entry	Name	Date	Job No	Dept	Hours
1	Dave	14/02/2022	21001	Packing	1.00
2	Dave	13/02/2022	21002	Packing	8.00
3	Jeff	14/02/2022	21003	Packing	6.00
4	Pete	14/02/2022	21004	Welding	8.00
5	Dave	14/02/2022	21005	Welding	0.50
6	Jeff	13/02/2022	21006	Welding	8.00
7	Dave	14/02/2022	21007	Welding	1.00
8	Pete	14/02/2022	21008	Packing	8.00
9	Pete	15/02/2022	21009	Packing	8.00
10	Dave	14/02/2022	21010	Welding	1.00
11	Dave	14/02/2022	21011	Packing	1.00

I would like a calculated column in Table1 that gives the department that the employee spent most of their time in for that day.

So in the example for Dave on the 14/02/2022 i would like to see "Welding", as he spent 2.5 hours in welding but only 2 hours in Packing on that day.

Thanks for your help

[v-zhangti · Accepted solution]

Hi, @paul_luvaglia 

You can try the following methods.

Table 2: Column

Sum = 
CALCULATE (
    SUM ( 'Table 2'[Hours] ),
    FILTER (
        'Table 2',
        [Name] = EARLIER ( 'Table 2'[Name] )
            && [Date] = EARLIER ( 'Table 2'[Date] )
            && [Dept] = EARLIER ( 'Table 2'[Dept] )
    )
)

Results = 
IF (
    [Sum]
        = CALCULATE (
            MAX ( 'Table 2'[Sum] ),
            FILTER (
                'Table 2',
                [Name] = EARLIER ( 'Table 2'[Name] )
                    && [Date] = EARLIER ( 'Table 2'[Date] )
            )
        ),
    [Dept]
)

Table 1: Column

DEPT = 
CALCULATE (
    MAX ( 'Table 2'[Results] ),
    FILTER (
        'Table 2',
        [Date] = EARLIER ( 'Table 1'[Date] )
            && [Name] = EARLIER ( 'Table 1'[Employee] )
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[paul_luvaglia]

Amazing, thank you. 

[Greg_Deckler]

@paul_luvaglia Try this in table 1:

Column =
  VAR __Employee = 'Table1'[Employee]
  VAR __Date = 'Table1'[Date]
  VAR __Table = SUMMARIZE(FILTER('Table2',[Employee] = __Employee && [Date] = __Date),[Dept],"Hours",SUM('Table2'[Hours]))
  VAR __Hours = MAXX(__Table,[Hours])
RETURN
  MAXX(FILTER(__Table,[Hours] = __Hours),[Dept])