How to create percentile rank dynamically based on slicers

[rchpradeep]

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

[v-piga-msft · Accepted solution]

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.

Hope this can help you!

Best Regards,

Cherry

[rchpradeep]

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.