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

[sjayawardana]

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,

[amitchandak]

@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])