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)