cancel
Showing results for
Did you mean:
Microsoft

## TOPN problem with duplicate values

Hi,

I have a table like this:

Player / Name / Quizizz Name have the same Name in all rows.

I now try to find the highest, 2nd highest, 3rd highest, ... number in the CPoints column. I am using these

1stPointsOverall = MAX(Results[CPoints])

2ndPointsOverall = MINX ( TOPN (2, VALUES(Results[CPoints]), CALCULATE(MAX(Results[CPoints])), DESC), CALCULATE(MAX(Results[CPoints])))

3rdPointsOverall =MINX ( TOPN (3, VALUES(Results[CPoints]), CALCULATE(MAX(Results[CPoints])), DESC),
CALCULATE(MAX(Results[CPoints])))

4thPointsOverall =MINX ( TOPN (4, VALUES(Results[CPoints]), CALCULATE(MAX(Results[CPoints])), DESC),
CALCULATE(MAX(Results[CPoints])))

etc.

the result is this:

But I want

1stPointsOverall   45

2ndPointsOverall  45

3rdPointsOverall  45

4thPointsOverall 35

5thPointsOverall  25

6thPointsOverall  25

7thPointsOverall  18

8thPointsOverall  0

When looking at the definitions of TopN, its says that it would return mutliple rows if double entries occur, but it seems not to do this the way I have set it up.

Any idea, how to get to my wanted result?

Thanks,

Markus

Super User

@MrDoubleG , create a new column like

rank col = rankx(table, [CPoints]+rand()/1000 ,,desc,dense)

Then try a measure

1stPointsOverall = MAX(filter(Table, Table[rank col] =1),Results[CPoints])

2ndPointsOverall = MAX(filter(Table, Table[rank col] =2),Results[CPoints])

refer tie breaker in rank

https://community.powerbi.com/t5/Community-Blog/Breaking-Ties-in-Rankings-with-RANKX-Using-Multiple-...

New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! Subscribe to my youtube Channel !!

Announcements