Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
avinash_patel
Frequent Visitor

RANKX Randomly Skipping Numbers

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:

 

powerbirankx.PNG

 

 

 

2 REPLIES 2
v-xicai
Community Support
Community Support

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.

 

7.png8.png

 

 

 

 

 

 

 

 

 

 

 

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)

 

9.png

 

 

 

 

 

 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.