Forum Discussion

TomLU123's avatar
TomLU123
Icon for Helper III rankHelper III
6 years ago
Solved

[Seek Help] How to compare the values across rows

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. 

 

RecruiterJob Requisition IDNumber of Candidate Hired by the recruiterTop Recruiter
AJ222451663A
BJ22245161A
CJ222451612A
AJ333111115C
CJ333111125C

 

Is it possible to build a caculated column like that?

 

Many thanks!

 

Best regards,

Tom

Need Help
  • az38's avatar
    az38
    6 years ago

    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)

2 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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)
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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!