Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |