Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
The formula to Calculate the conversion rate is as below:
conversion rate=num of offered role/num of interview complete candidates
1. I need to capture the output from a slicer=(Job Ad published date-date hierarchy activated-Year, Month)
2. Within the above period selected in the measure I need to count the number of positions that had status = offered role
3. Within the above period selected in the measure I need to count the number of positions that had status = interview complete
Candidate Name | Status | Recruiter Full Name | Job Ad Published date | Timestamp | Position |
Candidate 1 | New | PB | 01/01/2020 | 02/01/2020 | Support Worker |
Candidate 1 | Phone screened | KW | 01/01/2020 | 05/01/2020 | Support Worker |
Candidate 1 | Interview complete | PB | 01/01/2020 | 24/01/2020 | Support Worker |
Candidate 1 | Offered role | KW | 01/01/2020 | 22/02/2020 | Support Worker |
|
|
|
|
|
|
Candidate 2 | New | PB | 01/01/2020 | 03/01/2020 | Admin Officer |
Candidate 2 | Phone screened | KW | 01/01/2020 | 05/01/2020 | Admin Officer |
Candidate 2 | Interview complete | PB | 01/01/2020 | 25/01/2020 | Admin Officer |
Candidate 2 | Offered role | PB | 01/01/2020 | 30/01/2020 | Admin Officer |
For example: Slicer – Job Ad Published date= Month selected is January, Year =2020
Offered role num(should be counted from timestamp)= 1 candidate
Interview complete(based on timestamp)= 2 candidates
Conversion rate = 50%
Appreciate your help on this,
Solved! Go to Solution.
// You have to have a proper calendar
// for this. It'll be connected to
// Job Ad Published via one-to-many and then
// you can have another relationship
// to Timestamp but it'll be
// inactive. You can also have 2 calendars,
// each for the respective date field in
// the fact table, it's up to you.
// Let's assume this is the setup with just
// one calendar (the other one with 2 calendars
// is simpler). You shoul also create dimensions:
// Candidate, Recruiter, Status, Position.
// These would be connected to your fact
// table that needs to be hidden. Slicing
// only allowed via dimensions.
[# Offered Roles] =
// You have to get the candidates in the current
// context that 1) have Status = "Offered role"
// and 2) Timestamp is within the selected period.
// I'm assuming that a candidate cannot have 2
// entries in the fact table for the same ad
// with the same statuses. Should this be
// untrue, you'll have to adjust the code.
CALCULATE(
COUNTROWS( FactTable ),
KEEPFILTERS( Status[Status] = "Offered role" ),
USERELATIONSHIP( Calendar[Date], FactTable[Timestamp] )
)
[# Interviews Completed] =
CALCULATE(
COUNTROWS( FactTable ),
KEEPFILTERS( Status[Status] = "Interview complete" ),
USERELATIONSHIP( Calendar[Date], FactTable[Timestamp] )
)
[Conversion Rate] =
DIVIDE(
[# Offered Roles],
[# Interviews Completed]
)
// You have to have a proper calendar
// for this. It'll be connected to
// Job Ad Published via one-to-many and then
// you can have another relationship
// to Timestamp but it'll be
// inactive. You can also have 2 calendars,
// each for the respective date field in
// the fact table, it's up to you.
// Let's assume this is the setup with just
// one calendar (the other one with 2 calendars
// is simpler). You shoul also create dimensions:
// Candidate, Recruiter, Status, Position.
// These would be connected to your fact
// table that needs to be hidden. Slicing
// only allowed via dimensions.
[# Offered Roles] =
// You have to get the candidates in the current
// context that 1) have Status = "Offered role"
// and 2) Timestamp is within the selected period.
// I'm assuming that a candidate cannot have 2
// entries in the fact table for the same ad
// with the same statuses. Should this be
// untrue, you'll have to adjust the code.
CALCULATE(
COUNTROWS( FactTable ),
KEEPFILTERS( Status[Status] = "Offered role" ),
USERELATIONSHIP( Calendar[Date], FactTable[Timestamp] )
)
[# Interviews Completed] =
CALCULATE(
COUNTROWS( FactTable ),
KEEPFILTERS( Status[Status] = "Interview complete" ),
USERELATIONSHIP( Calendar[Date], FactTable[Timestamp] )
)
[Conversion Rate] =
DIVIDE(
[# Offered Roles],
[# Interviews Completed]
)
Thanks this works !
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |