cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TomLU123
Helper III
Helper III

[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

1 ACCEPTED SOLUTION
az38
Community Champion
Community Champion

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)

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

2 REPLIES 2
v-kellya-msft
Solution Sage
Solution Sage

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
Community Champion
Community Champion

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)

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!