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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Sammm888
New Member

Removing Ranking Duplicates

Hi all,

 

I have a duplicate ranking issue. At an overall level, it's fine, however bringing in multiple columns creates duplicate rankings.

Sammm888_0-1716461734659.png

 

As you can see overall is fine, but group 1 has multiple 12's. I want unique rankings for each category based on it's volume. This is my formula: 

Rank = RANKX (FILTER (ALL ('Table1'[SubCat]),'Table1'[SubCat] <> BLANK() && 'Table1'[SubCat] <> "UNTAGGED"),[Totalvols],,DESC)

I've gone to places like Chat GPT which ended up telling me to get help elsewhere. 
 
Thanks.
7 REPLIES 7
v-yohua-msft
Community Support
Community Support

Hi, @Sammm888 

First, create a Index in PowerQuery.

vyohuamsft_0-1716881246660.png

Then click Apply&Close.
Create a calculated column, and treat the Index you just created as a secondary sort, which must be uniquely identified and cannot have duplicate values. Otherwise, when your sort has the same value and the sub-sort value is the same, the rank will be the same. Here is my preview:

Column = ROWNUMBER(ORDERBY('Table'[Totalvols],DESC,'Table'[Index],ASC))

vyohuamsft_1-1716881684134.png

 

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yohua-msft - I dont agree with this solution, though you are correct, it removes the duplicates. In terms of ranking, the duplicates are legitamately there.

 

If a business user was to ask what the criteria was for separating the items that have 100 as a total volume, the answer would be "random chance" and that's not acceptable. It could lead to a poor decision being made by an organisation. 

mark_endicott
Super User
Super User

@Sammm888 - firstly you need to include all columns from your visual in the ranking, this is what is causing your duplication, secondly I would suggest putting the filtering on the visual and taking it out of the ranking. Please try the code below:

 

RANKX (ALLSELECTED('Table1'[SubCat], 'Table1'[Group]),[Totalvols],,DESC)

 

If you find you still need the filtering, you can use the ALLSELECTED from my code, rather than the ALL from yours. 

 

If this works, please accept this as the solution, if it does not, please supply some sample data for me to test. 

Sadly this is now ranking across all columns rather ranking for each individual column. I will try and provide sample data, but need to remove sensitive data first. 

Sammm888_0-1716468700358.png

 

@Sammm888 - Sorry for the late response, I have been travelling a lot the last 2 days.

 

I misread your challenge the first time, so my code was not going to work for you, sorry about that. 

 

Having seen your screenshot, I can see why you have duplicate rankings. In group 1 you have 3 [Totalvols] of 89, 3 [Totalvols] of 117 and 3 visible 153's.

 

These will all be producing duplicate ranks because they have the same value. If you want them to have different ranks, you need to provide another measure to break the ties.  

Hi, 

These are not the volumes, this is the output of your code. There are very few rows that have the same volumes

I see, then we still need some sample data to understand why you are getting duplicates. 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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