Calculate Percentiles Using Two Variables

[DHB]

I have created a percentile measure in my data using one column of data (ENGAGED_DAYS) but where more than one person has the same value (i.e. PERSON_CODE 2 and 3) I'd like to use another column as a tie-breaker (REQUEST_PER_ACTIVE_DAY). 

The data looks like this...

COURSE_CODE	PERSON_CODE	ENGAGED_DAYS	REQUESTS_PER_ACTIVE_DAY
MATHS101	1	10	15
MATHS101	2	15	11
MATHS101	3	15	17
MATHS101	4	20	10

I created this measure...

EngDays = SUM('All Faculty Course Activity Percentile 2022-04-04 - NO PERCENTILE'[ENGAGED_DAYS])

And this is the measure I created which calulates the percentile based on ENGAGED_DAYS only....

Course_Activity_Percentile =

VAR EngDays =

[EngDays]

RETURN

IF(

HASONEVALUE('All Faculty Course Activity Percentile 2022-04-04 - NO PERCENTILE'[PERSON_CODE]),

COALESCE(

DIVIDE(

--Numerator (below) counts values that are < the students's active day count in the course

CALCULATE(

COUNTROWS('All Faculty Course Activity Percentile 2022-04-04 - NO PERCENTILE'),

FILTER(

ALLEXCEPT('All Faculty Course Activity Percentile 2022-04-04 - NO PERCENTILE','All Faculty Course Activity Percentile 2022-04-04 - NO PERCENTILE'[CANVAS_COURSE_CODE]),

'All Faculty Course Activity Percentile 2022-04-04 - NO PERCENTILE'[ENGAGED_DAYS] < EngDays

)

),

--Denominator (below) counts the total students in the course

CALCULATE(

COUNTROWS('All Faculty Course Activity Percentile 2022-04-04 - NO PERCENTILE'),

ALLEXCEPT('All Faculty Course Activity Percentile 2022-04-04 - NO PERCENTILE','All Faculty Course Activity Percentile 2022-04-04 - NO PERCENTILE'[CANVAS_COURSE_CODE])

)

),

0

)

)

Can anyone help me to incorporate this additional factor into the calculation.

Thanks very much for your help.

[v-jingzhang]

Hi @DHB 

The first idea came to me is to multiply a small number (e.g. 0.0001) to REQUESTS_PER_ACTIVE_DAY and add this result to ENGAGED_DAYS to create a new adjusted ENGAGED_DAYS column with this additional factor. In this way, the new ENGAGED_DAYS column will not have duplicated values. Then you can use this new column in your percentile measure instead. 

new ENGAGED_DAYS = 'tablename'[REQUESTS_PER_ACTIVE_DAY] * 0.0001 + 'tablename'[ENGAGED_DAYS]

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[DHB]

Thank you @v-jingzhang. It doesn't matter that some people might have the same results in both measures as they can be given the same percentile in that case.  Adding the two numbers together would also mean that one loses primacy over the other.