Not working: Top N functionality (TOP N by "count" column if the count is repetitive)

[Samruddhi_11]

Hii,

As I shown in the above image, TOP N is not working. 
Explaination : There are 2 columns in my visual. 1. Pin name and 2. Count of pins. I want top 5 pin's by count of pins. here I applied filter on TOP N by count of pins but its not working. Its only giving me pins having 11 as a count which is max count. 11 count is repetative in this visual. There are other counts as well like 2, 4, 7 and 9 which are repetative but not showing rows with those counts in visual. Please help me in resolving this.
Thanks in advance!

[v-yalanwu-msft · Accepted solution]

Hi, @Samruddhi_11 ;

As far as I know, topn is to display the first few, first if you set topn5, then count=11 is the largest sort, but count=11 name is more than 5, then topn5 only display count=11;

For example, if you have four rows count=11 and two rows count=10, then topn5 displays six rows, Anyway, it's counting repeating columns.

so we could create a mesure.

rank = RANKX(ALLSELECTED('Table'),CALCULATE( COUNT('Table'[name])),,DESC,Dense)

Then could set less than or equal to .

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Samruddhi_11]

@v-yalanwu-msft  Thank you!

[amitchandak]

@Samruddhi_11 , based on what I got. Create a dense rank and use that in the filter in visual level filter for <= 5

rankx(allselected(Table[Pin name]), [Count of pin measure],,desc, dense ) 

[Samruddhi_11]

Hi @amitchandak , 
Thank you for your valuable solution but getting one problem if I select top 3 then only those rows are coming having count 2 and 4 . after 4 it should display rows with 7 count but its not showing so its not working for top 3 , 4 and 5. please help me in this. 

I attached 2 screenshots after applying top 3 in filter:

1. Ascending of count

2. Descending of count

Written Measure2 as you mentioned

Thanks!!

[amitchandak]

@Samruddhi_11 , can you share the measure. Also make sure in rank you have used measure, not just count(Table[Column]) , if you have used that then use calculate(count(Table[Column]))

[Samruddhi_11]

Hi @amitchandak ,

Below is a measure,

Measure2 =

RANKX(ALLSELECTED(cloud_pricing_export[sku]), cloud_pricing_export[Count of sku],,DESC,Dense)

where ,

cloud_pricing_export = table name

sku = pin name

count of sku = measure

Formula: 

Count of sku =

COUNT(cloud_pricing_export[sku])

[smpa01]

@Samruddhi_11  can be resolved a lot quicker, if you can provide sample data and expected output.

[Samruddhi_11]

I am not able to attach data file.

[Samruddhi_11]

There are 2 columns pin name and count of pins. I want top 5 pin names by count of pins. The only problem here is TOPN functionality of power BI is not working because the counts are repetative. if you have any query please do ask. Thanks!