Display TOP N by Count base on 1 Field

[cst_dev32]

Hy guys, i have an issue for my work

I have 1 column named PLNT and I need to display top 5 from that column base on their count

here's the data

The PLNT and the Count

And i filter by PLNT count

Filter TOP 5 base on PLNT CountBut the result showing 6 data not 5

The resultI just want to showing 5 PLNT not 6, how do it works?

[venug20]

@cst_dev32

you can achive by this way... it may helps... you can try....

Steps:

1. create index column

2. take index column into visual filter

3. you can choose your number in filter... then apply

If it is solution to your query. Please accept as a soluiton... it will helps to others....

[cst_dev32]

Hy @venug20

Thanks for reply my question

Can you tell me how to create the column index like that?

I just create the new calculate table like this

but i stuck on creating index like you did

can you show me the formula?

thanks

FYI : I use direct query, so i can't use edit queries

[venug20 · Accepted solution]

@cst_dev32

You can achive this in two ways

1st way: which already provide in above...

1.  go to menu bar select "Edit Queries"

2.  go add column "index column" 

3.  select "From 1" in index column

like below

2nd way: (If not available power query editor)

1. create one column for count

index = CALCULATE(COUNT('Rank'[Plant]), FILTER('Rank', 'Rank'[Plant] = EARLIER('Rank'[Plant])))

2. create quick measure for running total

index running total in Plant =
CALCULATE(
SUM('Rank'[index]),
FILTER(
ALLSELECTED('Rank'[Plant]),
ISONORAFTER('Rank'[Plant], MAX('Rank'[Plant]), DESC)
)
)

3. take "index running total in Plant" into visual filter chose <6

if it is solution for your query, please accept as solution...

[TomMartens]

Hey,

unfortunately this will not work using the TOPN filter from inside the visual, this is due to the tie,meaning the 4 PLNT with a value.

You have to decide which of the PLNT will not be selected, make this a business rule, create your own DAX and use this DAX statement to filter your data.

If you need help to create this DAX statement, please consider to provide a pbix file that contains sample data, upload the file to onedrive or dropbox and share the link.

Regards,

Tom