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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
carlG
Helper I
Helper I

Top N displaying more than N when duplicate values exist

Hi All,

 

Have an issue with Top N filter in PowerBI visual, when there is a duplicate ranked value. shows more than 5 results.

There a large amount of solutions around this forum and the web, but non seem to fit my use case after trialling them due to ranking 1-5.

 

RankX works, until there is a duplicate - even setting Dense or Skip for the tiebreaker does not work.

Tried various methods and fields, even creating a separate table, all with varying degrees of success.

 

Visual Appearance (Bottom 5)

carlG_2-1600136833166.png

Filter Settings

carlG_1-1600136810808.png

 

Data is populated by a likert survey ("Rank 1-5 how well you can use each item") and TopN filter shows bottom 5.

No additional data exists to modify the value as they are all submitted at the same time on the survey (No date hack method available)

 

Table Data Sample

SkillAverage of Rating Value
3D Scanner - Einscan Pro+2.5
Dynamic Microphone - Rode2.5
Gimbal - Zhiyun Crane32.5
Mixer - Rode Procaster2.5
PTX Camera System2.5
Resolume2.5
Video mic - Rode GO2.5
Volumetric Display - Looking Glass2.5
RTI Panels2.25
Volumetric Display - Voxon VX12.25
Chroma Key Kit2
Elgato Stream Deck2
1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@carlG Right, that' because of how RANKX and TOPN handle duplicates. You can add a small random number RAND()/100 to break ties. You'd have to create a measure to rank by like:

 

Measure = AVERAGE([Rating Value])*RAND()/100


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@carlG , In your Rank, adds a small random variable using Rand

 

Rank =  Rankx(<code>) +rank()/1000  and then use this in Top N filter

Greg_Deckler
Super User
Super User

@carlG Right, that' because of how RANKX and TOPN handle duplicates. You can add a small random number RAND()/100 to break ties. You'd have to create a measure to rank by like:

 

Measure = AVERAGE([Rating Value])*RAND()/100


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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