Calculations affected by another field other than the slicer

[sjayawardana]

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,

[daxer · Accepted 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] 
    )

[sjayawardana]

Thanks this works !