cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Calculations affected by another field other than the slicer

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,

1 ACCEPTED SOLUTION
Solution Sage
Solution Sage

// 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] 
    )

View solution in original post

2 REPLIES 2
Solution Sage
Solution Sage

// 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] 
    )

View solution in original post

Thanks this works !

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.