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

How to create percentile rank dynamically based on slicers

Hi,

 

I have the data as follows:

 

AgentStateYearOriginationsLapses Lapseratio
1Florida2017621660.10628
2Florida2017951980.103049
3Florida2017466910.195279
4Florida2017614720.117264
5Florida2017836730.087321
6Florida2017235740.314894
7Florida2017221160.072398
8Florida201793880.008529
9Florida2017280740.264286
10Florida2017834420.05036
1Texas2017406180.044335
2Texas2017375880.234667
3Texas2017753670.088977
4Texas2017312520.166667
5Texas2017581320.055077
6Texas201758820.003401
7Texas2017748900.120321
8Texas2017417640.153477
9Texas2017618240.038835
10Texas2017349200.057307

 

Lapse ratio = lapse/orginations and it should dynamic based on filters i.e., if we dont select any country lapse ratio = sum(lapse)/sum(originations)

 

I am trying to replicate percentile rank in excel using the following method:

 

Rank = Rankx(rawdata, rawdata[Lapseratio])

 

Pctrank = (CountA(rawdata[lapseratio])-rawdata[rank])/CountA(rawdata[lapseratio])-1

 

But am getting the error 'Circular dependancy' as I passed the measure into a calculated column.

 

If I use rank as a measure, am getting incorrect ranks.

 

Can anyone suggest me how to calculate percentile ranks dyanamically?

 

Thanks,

Chaitanya

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: How to create percentile rank dynamically based on slicers

Hi @rchpradeep,

 

Based your description, I have made a test which could reproduce your issue.

 

Assuming that you create a measure lapse retion with sum(lapse)/sum(originations).

 

I suggest you could create a measure with Rankx.

 

Rank = Rankx(ALLSELECTED(rawdata),[lapse retion])

Then you should get the right rank.

rankx.PNG

 

Hope this can help you!

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: How to create percentile rank dynamically based on slicers

Hi @rchpradeep,

 

Based your description, I have made a test which could reproduce your issue.

 

Assuming that you create a measure lapse retion with sum(lapse)/sum(originations).

 

I suggest you could create a measure with Rankx.

 

Rank = Rankx(ALLSELECTED(rawdata),[lapse retion])

Then you should get the right rank.

rankx.PNG

 

Hope this can help you!

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
rchpradeep Frequent Visitor
Frequent Visitor

Re: How to create percentile rank dynamically based on slicers

Hi Cherry,

 

Thanks for your response.

 

It's working. Can you please explain me how the usage of 'All selected' solved this issue? Are we imposing row context in a measure by using 'All selected' ?

 

Thanks a lot for your help!

 

Regards,

Chaitanya.