Filtering Top five client based on some criteria


Hi All,

Need your help to find Top 5 Client excluding Client B, C, D, E.

Any help will be greatly appreciated.


Here’s what I did -

Created an intermediate table to filter out the excluded clients:

 FilteredTestTable = 
        (VALUES ( Test )),
        (Test[Client] <> "B"
        && Test[Client] <> "C"
        && Test[Client] <> "D"
        && Test[Client] <> "E")

Then created two measures, one to rank the clients in the filtered table by price, and the second to identify clients with a top 5 rank.

RankCalc = 
    ALL ( FilteredTestTable[Client] ),
    CALCULATE ( SUM ( FilteredTestTable[Price] ) )

Top5 = 
    IF (
        [RankCalc] <= 5,


I’m sure others more proficient at DAX can come up with a more elegant solution that doesn’t require the creation of an intermediate table, but I hope this is helpful.

  • Brian

First question to answer in your model, will it always be B,C,D,E or do you need a more dynamic approach, in which case a secundary table is the way to go.
Study DAX formulae RANK and TOPN, below a link to TOPN.
Check the video Top 3 Salespeople… in the Business Analytics Series.



Hi Brian,

Thank you very much.

I came across this short video today that showed a clever way to exclude items/clients via slicer that might be useful to you if the specific clients you want to exclude change over time:

1 Like

Looks great. Nice idea