Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rchpradeep
New Member

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
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

View solution in original post

2 REPLIES 2
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.