Employment conversion rate measure calculation

[SadStatue]

Dear community,

Below you can find the data related to a recruitment process. What I want is to calculate a conversion rate between 2 phase of this process (Stat 11 to 20).

Timestamp	Reference ID	Name	stat	highlighted
7/08/2020 0:04	C_a118	name1147	1
14/08/2020 16:08	C_a118	name1147	11	X
27/08/2020 9:45	C_a118	name1147	20
7/08/2020 2:32	C_a118	name1291	1
4/08/2020 14:12	C_a118	name0630	11	X
6/10/2020 13:15	C_a127	name0325	1
22/09/2020 10:52	C_a127	name0544	11
21/08/2020 2:49	C_a129	name1323	1
22/09/2020 15:51	C_a129	name1323	11	X
14/09/2020 9:14	C_a129	name1324	11	X
12/10/2020 9:24	C_a129	name1323	20
14/10/2020 9:14	C_a129	name1326	11

each time the stat column change we record it in a row with a time stamp. Refrence_ID shows the position we want to recruit and name is the name of the applicant. and stat show the status of applicant for the process. what I want to calculate is the conversion rate between stat 11(just those records with the stat of 11 that highlighted with X should be counted for my measure) to 20.

Conversion rate = (distinct number of applicant who have stat of 20) / (distinct number of applicants who have stat of 11 for the same positions that somebody get stat 20 and with a time stamp equal or less than the max timestamp of the person get stat 20 for that position)

for this very little example the result should be sth like this:

conversion rate=2/4=0.5

Does anyone can help me write the related dax measure?

Thank you in advance for your help and support!

Here you can find the sample nad real data !

[v-xulin-mstf]

Hi @SadStatue,

Create two measures as:

Measure =

var _reference=CALCULATE(MAX('Table'[Reference ID]),FILTER('Table','Table'[Molecular]=1))

var _datestamp=CALCULATE(MAX('Table'[Timestamp]),FILTER('Table','Table'[Molecular]=1 && 'Table'[Reference ID]=_reference))

var  a=CALCULATETABLE(VALUES('Table'[Name]),FILTER(ALL('Table'), 'Table'[stat]=20))

var  b=CALCULATETABLE(VALUES('Table'[Name]),FILTER(ALL('Table'),'Table'[Timestamp]<_datestamp && 'Table'[stat]=11&&'Table'[Reference ID]=_reference))

Return

COUNTROWS(a)/COUNTROWS(b)

Molecular =

IF(MAX('Table'[stat])=20,1,0)

Here is the output:

Here is the demo, please try it: Employment conversion rate measure calculation

Best Regards,

Link

[SadStatue]

Hi @v-xulin-mstf 

Thank you so much for your time and effort on my problem.

i dont think its working as I implement it for the data I've mentioned in the main post and it brings infinity. So I believe variable "b" is not calculating as we want.

Cheers,

[v-xulin-mstf]

Hi @SadStatue,

I have modified the measure.

Here is the output:

Here is the demo, please try it: Employment conversion rate measure calculation

Best Regards,

Link

[SadStatue]

Hi @v-xulin-mstf ,

Thank you so much for your time and review.

I believe we get closer to the soloution, but the issue with the current measure is that we get the max refrenceID. so it works if we filter by refrenceID but when we want it for all the refrence IDs its not working as it consider the max one.

in fact we need to calculate "a" and "b" for each refrence ID and then calculate Sum(a)/Sum(b)
we may use the function Earlier, but im not sure how should I use it.

Cheers,

[v-xulin-mstf]

Hi @SadStatue,

I'm not sure if you don't want to group by "refrenceID" but to count the total.

Maybe the measure below works:

Measure = 
var _reference=CALCULATE(MAX('Table'[Reference ID]),FILTER('Table','Table'[stat]=20))
var _datestamp=CALCULATE(MAX('Table'[Timestamp]),FILTER('Table','Table'[stat]=20 && 'Table'[Reference ID]=_reference))
var  a=CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        ALL('Table'),
        'Table'[stat]=20
    )
)
var  b=CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        ALL('Table'),
         'Table'[stat]=11 && 'Table'[Timestamp]<=_datestamp
    )
)
Return
a/b

Best Regards,

Link

[SadStatue]

Hi @v-xulin-mstf ,

Thank you for your response. But I dont think it works properly as we released the condition of Refrence ID. I still want to calculate number of stat 11 just for those refrences that had stat 20.
I'm not sure how groupby can help me in this situation. but I need the number of records.

Cheers,

[v-xulin-mstf]

Hi @SadStatue,

I'm not clear.

For example, if you want to calculate (count of green mark)/(count of blue mark) when i select C_a129.

Best Regard,

Link

[SadStatue]

Hi @v-xulin-mstf ,

Thanks for your response.

it works if we select a single Reference ID, but it wont work if we dont filter it by a single Ref ID. And I want it to work generaly.

below example is more comprehensive:

So I want the (count of red underlined)/(count of highlighted).

Cheers,

[v-xulin-mstf · Accepted solution]

Hi @SadStatue,

I updated the demo, please refer: Employment-conversion-rate-measure-calculation 

Here is the output:

Best Regards,

Link

[SadStatue]

Hi@v-xulin-mstf 

Thanks for your response. I used your suggested solution but I noticed that its not working properly!! as we manage part of the filtering in a column which make it so undynamic!
I believe we have to manage the whole filtering and calculation in measure level and not in the columns as I want to show these conversion rates in bar chart (conversion rate per month).
so it calculate all the record with stat 20 and devide by all record with stat 11 and column=1. so our measure fail to filter stat 11s that have the same reference_ID (position).
It would be great if you can have another look on it

Cheers,

[SadStatue]

Hi @v-xulin-mstf ,

Thank you so much for your time and effort to find the solution. Its woorking perfectly.

Cheers,

[Ashish_Mathur]

Hi,

So the numerator has to be the count of all rows that have 20 in the stat column and the denominator has to be all rows that have 11 in the stat column.  Am i correct?  Share the link from where i can download your PBI file.

[SadStatue]

Hi @Ashish_Mathur ,

Thank you for your comment. No I have some more considerations in calculation of this conversion rate. I want to find the rate that 11 (Interviewed applicant) turned to 20 (Employed). So there should be an 20 for counted 11 (on the same ref ID/job position) and also the 11 time stamp should be before the time stamp of related (on the same ref ID) 20 .
in the sample data there are two 11 that I dont want to count as below:

You can find the whole data here, but lets start working with the sample data.

[amitchandak]

@SadStatue , Try a measure like

divide(calculate(distinctcount(Table[Name]), filter(Table, Table[stat] = 20)),calculate(distinctcount(Table[Name]), filter(Table, Table[stat] = 11)))

[SadStatue]

Hi @amitchandak 

Thank you for your quick response.

No it wont work as u just simply filter just by stat while I have 2 more conditions (it is 2/6=0.33 which is not what I want)

1. Calculating number of applicant with stat 11 for the same positions(Reference ID) that sb get stat 20

2. Time stamp of records with stat 11 should be equal or before the time stamp of the records with stat 20 for that position (Reference ID)

I've amended the main post sample data and marked those records with sat of 11 that want to be calculated in my measure with X. As u see there are 2 records with stat of 11 which is not marked one of them is for C_a 127, as no one get stat 20 for this reference ID (position) and the other one is the last line record, and its not marked as its time stamp is after the time stamp of the record one to last (the one that have stat 20 for the same refrence ID)

You can also find the whole data and a larger sample data in the hyper link of the main post.

I hope I could shed some light on the issue by this extra description.

Cheers,