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