Filtering Top five client based on some criteria

DAX DAX Calculations
1

1

Hi All,

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

Any help will be greatly appreciated.

2

Nbaraili,

Here’s what I did -

Created an intermediate table to filter out the excluded clients:

 FilteredTestTable = 
    CALCULATETABLE (
        (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 = 
RANKX (
    ALL ( FilteredTestTable[Client] ),
    CALCULATE ( SUM ( FilteredTestTable[Price] ) )
)

Top5 = 
    IF (
        [RankCalc] <= 5,
        1,
        0
    )

Results%20Table

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
3

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

image
image705×424 106 KB

Paul

Enterprise%20DNA%20Expert%20-%20Small

4

Hi Brian,

Thank you very much.

5

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
6

Looks great. Nice idea