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). Timestamp Referen...
Need Help
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

  • SadStatue's avatar
    SadStatue
    Helper II
    5 years ago

    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,

      • SadStatue's avatar
        SadStatue
        Helper II
        5 years ago

        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,