cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Super User VI
Super User VI

Re: [Seek Help] How to compare the values across rows

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
Highlighted
Super User VI
Super User VI

Re: [Seek Help] How to compare the values across rows

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

Highlighted
Solution Sage
Solution Sage

Re: [Seek Help] How to compare the values across rows

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!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors