Forum Discussion

cst_dev32's avatar
cst_dev32
Frequent Visitor
7 years ago
Solved

Display TOP N by Count base on 1 Field

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?

Need Help
  • venug20's avatar
    venug20
    7 years ago

    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...

     

     

4 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    7 years ago

    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

  • venug20's avatar
    venug20
    Resolver I
    7 years ago

    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's avatar
      cst_dev32
      Frequent Visitor
      7 years ago

      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's avatar
        venug20
        Resolver I
        7 years ago

        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...