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
Anonymous
Not applicable

Remove Duplicate Ranking

Dear All,

I want unique ranking for all records having same values in column.

As shown in below image, 2 users having same amount so ranking is same 8, 8 for both but I want 8 and then 9 for other user.

How can I do it. Kindly help me.

rank example = RANKX(ALLSELECTED(Farmer_Transactions[RetailerCode]), CALCULATE(SUM(Farmer_Transactions[Quantity])))
 
bi.png
1 ACCEPTED SOLUTION

HI @Anonymous ,

 

The last 2 Sales Person have the same amount of Sales and that is why it is showingin the TOP 10 employees by Sales.

 

Incase you want to show 10 employees, you will need to break the ranks ( but this will not give a clear picture to the person whom you are sharing this dashboard) as both Rajesh and Rishant has 97 units of Sales.

 

 

Anyways incase you want to break ties.

 

Create a new measure

 

Randome = RAND()
 
 
Add [Randome] to Rank Measure
 
Rank Employee =
var a = RANKX(ALL(Sheet1[RetailerName]),[Quantity Sold per Employee]) + [Randome]
RETURN
a
 
1.jpg
 
 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

 

View solution in original post

15 REPLIES 15
v-diye-msft
Community Support
Community Support

Hi @Anonymous 

 

If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly. If not, please kindly elaborate more. thanks!

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Anonymous
Not applicable

Thank you all for your help and sorry for delayed responce. but still my issue is not resolved....

not able to get top 10 retailers . Below issue is coming -

bi.png

 

or please help me with some other expression

Hi @Anonymous ,

 

Are you creating  Calculated Column or a measure?

And the above formula , is to rank between a group I.e Categories and Subcategories.

 

Can you pls share some sample Data in text format and expected output.

 

And also pls specify measures or Calculated Columns.

 

Regards,

HN

 

 

Anonymous
Not applicable

Dear Harsh,

 

I am sending you sample data for your reference. 

I have 3 columns Retailer name, Date  and Quantity sold.. I need top 10 reatilers based on their sale quantity and need below output of top 10 retailers-

 

Please download pbix file from here -

https://www.sendspace.com/file/7bgm50

 

bi.png

 

Hi @Anonymous ,

 

Can you pls upload it on Google Drive.

 

Not able to access this file.

 

Regards,

Harsh Nathani

Anonymous
Not applicable

Hi @Anonymous ,

 

You need to provide access to the file.

 

Regards,

HN

Anonymous
Not applicable

Hi @harshnathani , sorry for the botheration you faced.

Access is granted to you..Kindly let me know in case of any isssue

HI @Anonymous .

 

 

Please find link to the file

 

https://drive.google.com/file/d/1ezsx_2BbNpCqkF3dku7aQ0jrLX5sQqVw/view?usp=sharing

 

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Anonymous
Not applicable

Dear Harsh,

Thanks for your help.

Just I have only query. May be it is my silly question and may be not possible..but please clear my doubt.

As you shown in the chart, it is showing 11 retailers data as they are falling in below 11 rank.

but can I show only top 10 retailers as shown in below chart -

bi.png

HI @Anonymous ,

 

The last 2 Sales Person have the same amount of Sales and that is why it is showingin the TOP 10 employees by Sales.

 

Incase you want to show 10 employees, you will need to break the ranks ( but this will not give a clear picture to the person whom you are sharing this dashboard) as both Rajesh and Rishant has 97 units of Sales.

 

 

Anyways incase you want to break ties.

 

Create a new measure

 

Randome = RAND()
 
 
Add [Randome] to Rank Measure
 
Rank Employee =
var a = RANKX(ALL(Sheet1[RetailerName]),[Quantity Sold per Employee]) + [Randome]
RETURN
a
 
1.jpg
 
 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

 
Anonymous
Not applicable

Thank you so much Harsh providing me the solution that I required.

But yes, you are right... my supervisor also saying the same thing that let the 11th person come in the dashboard because he also sold the same quantity as 10th one.

I am using your solution.. Thanks a lot to all 🙂 🙂

 

 

Pragati11
Super User
Super User

Hi @Anonymous ,

 

Just check my blog on handling duplicate rankings here:

https://community.powerbi.com/t5/Community-Blog/Power-BI-Handling-Duplicate-Rankings-Using-the-RANKX-DAX/ba-p/1010574

 

This should help you.

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

harshnathani
Community Champion
Community Champion

Hi @Anonymous ,

 

 

https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

 

This article explains how you can create unique ranks.

 

Regards,

Harsh Nathani

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.