[Seek Help] How to compare the values across rows

[TomLU123]

Hi Expert,

I am try to create an evaluation column to identify who is the recruiter that hired the most candidates of the job req. Example as below. A job Req can have multiple recruiters. Each recruiter may successfully hired different number of candidates. The column should return the Top recruiter who hired the most of the job. 

Recruiter	Job Requisition ID	Number of Candidate Hired by the recruiter	Top Recruiter
A	J2224516	63	A
B	J2224516	1	A
C	J2224516	12	A
A	J3331111	15	C
C	J3331111	25	C

Is it possible to build a caculated column like that?

Many thanks!

Best regards,

Tom

[Anonymous]

Hi @TomLU123,

Or you can create a measure as below:

Top Recruiter = 
var _max=MAXX(FILTER(ALL('Table'),'Table'[Job Requisition ID]=MAX('Table'[Job Requisition ID])),'Table'[Number of Candidate Hired by the recruiter])
Return
CALCULATE(MAX('Table'[Recruiter]),FILTER(ALL('Table'),'Table'[Number of Candidate Hired by the recruiter]=_max))

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[az38 · Accepted solution]

Hi @TomLU123 

it's unclear how are you going to solve a tie-break situation but in the most common case you can use a column statement like

Top Recruiter = 
var _max = CALCULATE(MAX(Table[Number of Candidate Hired by the recruiter]), ALLEXCEPT(Table, Table[Job Requisition ID]))

RETURN
CALCULATE(FIRSTNONBLANK(Table[Recruiter], 1), ALLEXCEPT(Table, Table[ob Requisition ID]), Table[Number of Candidate Hired by the recruiter] = _max)