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
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!:
Mastering Power BI 2nd Edition

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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.