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
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Oros 

 

You are using this measure in Filters pane to filter [ITEM TOP in SelectedN]=1 to display the Items you need, modify this one a little bit

ITEM TOP in SelectedN =
IF (
    SELECTEDVALUE ( yourTable[yourItemColumn] )
        IN { "yourItem1Name", "yourItem2Name" },
    0,
    IF ( [ITEM RANKING] <= [ITEM TOPSELECTED], 1, 0 )
)
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)