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,

 

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

    • SadStatue's avatar
      SadStatue
      Helper II
      5 years ago

      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,