Forum Discussion

Samruddhi_11's avatar
Samruddhi_11
Icon for Helper I rankHelper I
4 years ago
Solved

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

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 ap...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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's avatar
Samruddhi_11
Icon for Helper I rankHelper I
4 years ago

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's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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's avatar
    Samruddhi_11
    Icon for Helper I rankHelper I
    4 years ago

    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's avatar
      smpa01
      Icon for Community Champion rankCommunity Champion
      4 years ago

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

      • Samruddhi_11's avatar
        Samruddhi_11
        Icon for Helper I rankHelper I
        4 years ago

        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!