adding up parts of a table and returning a column that has a person with the highest subtotal

[CL7777]

I consider myself an advanced beginner DAX user and Ive stumbled on this issue that I feel like should be relatively easy to solve, but I cant figure it out. I have a table that has employee name, job number, hours worked and a few other columns. I want to add up the hours on each job for each employee that worked on a particular job and return (in a calculated column) the name of the employee that spent the most time on each job... Below is a simplified version of that table that I have. I want a dax expression that returns the last column. For job 1, John spent more total time on it than the others so it returns John. For Job 2, susan spent the most total time on it so I want it to return Susan.

Thanks for the help!

employee name	job number	hours worked	desired RETURN COLUMN
john	1	7	john
dave	1	4	john
harry	1	3	john
susan	1	2	john
john	1	2	john
dave	1	1	john
harry	1	4	john
susan	1	6	john
john	2	6	susan
dave	2	4	susan
harry	2	9	susan
susan	2	11	susan
john	2	5	susan
dave	2	5	susan
harry	2	1	susan
susan	2	4	susan

[parry2k · Accepted solution]

@CL7777 add following two columns, first rank by hours worked and then get the name based on the rank.

Rank = 
RANKX ( 
    FILTER( Job,  Job[job number] = EARLIER( Job[job number] ) ), 
    Job[hours worked], , 
    DESC 
) 


emp = 
CALCULATE ( 
    MAX ( Job[employee name] ), 
    ALLEXCEPT ( Job, Job[job number] ),
    Job[Rank] = 1 
) 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[CL7777]

Thats really cool. thank you