I have the data as follows:
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?
Go to Solution.
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!
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!
Power BI Super User, Greg Deckler, explains
Register by September 5 to save $200
Engage and empower students with Power BI!
Continue your learning in our online communities.