cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Denominator should only consider the roles considered for the numerator in ratio calculations

Hi All,

 

My data table will look like this

Candidate Name

Status

Recruiter Full Name

Job Ad Published date

Timestamp

Job Role

Candidate 1

New

PB

01/01/2020

02/01/2020

Support Worker

Candidate 1

Phone screened

KW

01/01/2020

05/01/2020

Support Worker

Candidate 1

Interview complete

PB

01/01/2020

24/01/2020

Support Worker

Candidate 1

Offered role

KW

01/01/2020

22/02/2020

Support Worker

 

 

 

 

 

 

Candidate 2

New

PB

01/01/2020

03/01/2020

Admin Officer

Candidate 2

Phone screened

KW

01/01/2020

05/01/2020

Admin Officer

Candidate 2

Interview complete

PB

01/01/2020

25/01/2020

Admin Officer

Candidate 2

Offered role

PB

01/01/2020

30/01/2020

Admin Officer

 

 

 

 

 

 

 

 

 

 

 

 

Candidate 3

New

PB

01/01/2020

03/01/2020

Finance Officer

Candidate 3

Phone screened

KW

01/01/2020

05/01/2020

Finance Officer

Candidate 3

Interview complete

PB

01/01/2020

25/01/2020

Finance Officer

Candidate 3

Offered role

KW

01/01/2020

30/01/2020

Finance Officer

 

Conv.Rate should be= Candidates with offered Roles #(all roles offered within the current month)/Candidates with Interviews completed#(only for the offered positions in the numerator)

I created a date table based on the max and min of the dates job was offered

The result should be like this :

  1. There are 3 roles A, B,C advertised

Month

Offered Role

Interview Complete

Offered role/int complete- per month- expected result

September

-

5 for role A, 5/B

0

October

1/A

6/A, 6/C

1/11

November

1/A, 2/B

7/A, 7/B

3/30-denominator only considers the roles considered for the numerator

 

 

 

 

 

Appreciate your help on this.

Thanks,

1 REPLY 1
Super User IV
Super User IV

@sjayawardana , Make sure you have month year column in the table of date table This measure will give you the interview completed and offered role in same month

 

countx(filter(summarize(Table, Table[Candidate], Table[Month Year], "_1", calculate(count(Table[Candidate]),filter(Table, [Status] ="Interview complete"))
, "_2", calculate(count(Table[Candidate]),filter(Table, [Status] ="Offered role"))), [_1] =[_2] && [_2] >0),[Candidate])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

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!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors