Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Team,
Need confirmation whether we can get rank of measure having rankx.
I am trying this but its not giving me proper rank. please let me know if i am doing the right way.
Basically have Customers list with their avg sell price. I have created
CustomerRank = RANKX(ALLSELECTED(table[CUSTOMER])
,CALCULATE(AVERAGE(table[SELL PRICE USD]))
,
,DESC
)+RAND()
and then i am creating Test measure which calculates rank of Customer on CustomerRank measure
Test =
RANKX(ALLSELECTED(table[CUSTOMER])
,[CustomerRank]
,
,ASC
)
Is there a different way to calculate rank over rank measure ?
Attached screen for ref:
Hi @avinash_patel ,
According to your description, I create sample data to test the scenario. You can delate the "+RANK()" from your DAX formula, then it will work fine in default skip rank.
If you need it in the continuous rank, you can add "Dense" in DAX. You can learn more about RANKX().
CustomerRank = RANKX(ALLSELECTED('table'[CUSTOMER]),CALCULATE(AVERAGE('table'[SELL PRICE USD])),,DESC,Dense)
Test = RANKX(ALLSELECTED('table'[CUSTOMER]),[CustomerRank],,ASC,Dense)
Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EdxCSFkvUUtNj_xfIP...
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Amy,
Thank you for response.
I am doing "+Rand()" to CustomerRank measure to make each rank distinct.
And then again trying to rankx customer using the earlier rank measure which will break the ties in Rankx.
Even if in every call rand() function will recaluclated and will have different values. Since it will only provide numbers between 0 to 1, no two ranks can be same since its a random function.
In ideal world scenario, it should work right? but it magically skips few ranks. 😞
Ranking is very important as in very dashboard, reports for analysis higher management only wants to see top/bottom combinations of data points. Any jumbled up data will raise tons of question and I don't know how will I explain ranking system ties etc etc to business guys. Moreover if some higher authority dont like it, they will simply ask to share the ppt with corrected data, thus killing the purpose of powerbi report.
Regards,
Avi
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |