Forum Discussion

Oros's avatar
Oros
Post Prodigy
5 years ago

Exclude items from TOPN

Hello.   I have a report with TOP N.  I created a new TOP TABLE and put the index top 5, top 10 and top 15.  I created a measure to add in the filter of the report: ITEM TOP in SelectedN = IF([IT...
Need Help
Oros's avatar
Oros
Post Prodigy
5 years ago

Hi Anonymous ,

 

Thank you very much for your reply.  It works in filtering out the items to be excluded.  But it is not replacing those excluded items.

 

For example if the Top 5 items are A, B, C, D, E and you would like to exclude items D and E, the TOP 5 should be items A, B, C, F, G  and not just A, B, and C.  The Top 5 items should still show 5 items and not 3 itmes.

 

The same thing if Top 10 is selected, if any there are two (2) items excluded, the Top 10 should take the 11th and the 12th to replace the excluded items.

 

Thanks again.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Oros 

 

I misuderstood it, so when you do RANKX, you need to filter the items out first

  • Oros's avatar
    Oros
    Post Prodigy
    5 years ago

    Hi Anonymous ,

     

    Where exactly should I filter the items here?  Thanks.

     

    ITEM RANKING = RANKX(ALL(Product_Table[Item]),Product_Table[Sales],, DESC)
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Oros 

       

      try it

      ITEM RANKING = RANKX(FILTER(ALL(Product_Table[Item]),Product_Table[Item])<>"yourItem1"&&Product_Table[Item])<>"yourItem2"),Product_Table[Sales],, DESC)
      • Oros's avatar
        Oros
        Post Prodigy
        5 years ago

        Hi Anonymous ,

         

        It does not look like it's working.  Maybe it's just missing some characters?

         

        Attached is the screenshot directly from Power Bi.  Just for the purpose of this example, I replaced the original table name to 'Product_Table'. Please ignore the spaces atthe beginning and end of 'Product_Table'.  But how it looks in Power Bi with the original table name is the same.

         

        Thanks.