Forum Discussion

SadStatue's avatar
SadStatue
Helper II
5 years ago
Solved

Employment conversion rate measure calculation

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

TimestampReference IDNamestathighlighted
7/08/2020 0:04C_a118name11471 
14/08/2020 16:08C_a118name114711X
27/08/2020 9:45C_a118name114720 
7/08/2020 2:32C_a118name12911 
4/08/2020 14:12C_a118name063011X
6/10/2020 13:15C_a127name03251 
22/09/2020 10:52C_a127name054411 
21/08/2020 2:49C_a129name13231 
22/09/2020 15:51C_a129name132311X
14/09/2020 9:14C_a129name132411X
12/10/2020 9:24C_a129name132320 
14/10/2020 9:14C_a129name132611 

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 !

Need Help

15 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      SadStatue
      Helper II
      5 years ago

      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,

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Community Support
    5 years ago

    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