cancel
Showing results for
Did you mean:
Frequent Visitor

## How to create percentile rank dynamically based on slicers

Hi,

I have the data as follows:

 Agent State Year Originations Lapses Lapseratio 1 Florida 2017 621 66 0.10628 2 Florida 2017 951 98 0.103049 3 Florida 2017 466 91 0.195279 4 Florida 2017 614 72 0.117264 5 Florida 2017 836 73 0.087321 6 Florida 2017 235 74 0.314894 7 Florida 2017 221 16 0.072398 8 Florida 2017 938 8 0.008529 9 Florida 2017 280 74 0.264286 10 Florida 2017 834 42 0.05036 1 Texas 2017 406 18 0.044335 2 Texas 2017 375 88 0.234667 3 Texas 2017 753 67 0.088977 4 Texas 2017 312 52 0.166667 5 Texas 2017 581 32 0.055077 6 Texas 2017 588 2 0.003401 7 Texas 2017 748 90 0.120321 8 Texas 2017 417 64 0.153477 9 Texas 2017 618 24 0.038835 10 Texas 2017 349 20 0.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
Highlighted
Community Support Team

## Re: How to create percentile rank dynamically based on slicers

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.

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
Highlighted
Community Support Team

## Re: How to create percentile rank dynamically based on slicers

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.

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.
Frequent Visitor

## Re: How to create percentile rank dynamically based on slicers

Hi Cherry,

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.

Announcements

Power BI Super User, Greg Deckler, explains

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 144 members 2,036 guests
Recent signins: