Workaround to show TOP N where there are duplicates

[SusuYes]

Hello everyone, 

I have the following table that lists all the shifts published this year. Each shift is a separate row. 

I'm trying to show the Top 5 staff with the most shifts by using a Top N filter on Hours however since I have staff with the same hours, I'm getting more than 5 results. I understnad that this is where the RANKX fucniton can be useful but I was not able to apply it to my case. 

Any hints or ideas? 

[naveenmechu]

Hi,

Create calculated column as- 

Rank = RANKX('Table1',

'Table1'[Cost], ,,Dense

)

You will get Dense Rank you can use it according to requirement.

Please mark it as as solution it soles your query.

Thanks

Naveen Mechu

[SusuYes]

I added the calculated coloumn but my graph is still showing more than 5 entries. My calculated coloumn is 'Top 5 by Hours'

[naveenmechu]

Hi ,

Just put this Calculated Column as a simple filter and select Rank less than or equal to 5.

Thanks

Naveen Mechu

[SusuYes]

Just tried that too  but no changes happen to the visual at all. 

[ALLUREAN]

Hi, @SusuYes 

Try something like:

RANX Hours DENSE =

RANKX(

‘YourTable’,

‘YourTable'[Hours],

,

,

Dense

)

You can also try top5 measure like:

Top5 =
VAR TopN = 5
VAR ranks =
RANKX(
‘YourTable’,
‘YourTable'[Hours],
,
,
Dense
)

RETURN
IF ( ranks <= TopN, ranks, BLANK () )

[SusuYes]

I tried adding the first measure but I got this error. 

I tried adding SUM(TABLE NAME[Hours]) but now it's just showing the value 1 for all entries

any idea what went wrong? 

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  Via text boxes, show the problem clearly and also the expected result.

[SusuYes]

Hello, sorry about that, can you try this: https://bit.ly/31TtAkR

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[SusuYes]

Here is a link to a sample  PBI file: https://bit.ly/3Fi1QEc

I want my bar graph to only show the top 5 staff who have the most hours. The bar graph should only show 5 enteries only. 

[Ashish_Mathur]

Hi,

That link has expired.

[SusuYes]

I cant share the document as it has sensitive data but I will create a sample and share soon.