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
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,

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

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

      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's avatar
        v-xulin-mstf
        Community Support
        5 years ago

        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