Top 5 and Bottom 5

DAX DAX Calculations
1

Hi all,

I have a list of customers and want to create a visual that shows only the top 5 and the bottom 5.

Any suggestions?
Thanks

2

@JoaoMonteiro

VAR AllNonBlankCustomers =
    FILTER ( ALL ( Customer[Name] ), [Total Sales] <> 0 )
VAR TopCustomers =
    TOPN ( 5, AllNonBlankCustomers, [Total Sales], DESC )
VAR BottomCustomers =
    TOPN ( 5, AllNonBlankCustomers, [Total Sales], ASC )
VAR CustomersAppended =
    UNION ( TopCustomers, BottomCustomers )
VAR Result =
    CALCULATE (
        [Total Sales],
        KEEPFILTERS ( CustomersAppended )
    )
RETURN Result
3

Hi @AntrikshSharma thanks for your help.

I was almost there but, I don’t know why, it wasn’t working…
My code is more complicated, but I think it should work.

If you can explain this to me: why if I put VALUES instead of ALL the measure shows all the clients and not the top5 bottom5?

VAR AllNonBlankCustomers =
FILTER ( VALUES( Clients[Summary client]), [Order revenue] <> BLANK())

Regards
JM

4

@JoaoMonteiro That’s because at each cell of the report VALUES will return only 1 client because it respects the filter context, but what you need is ALL clients at every cell so that you can only pick what you need, that’s why you need to use ALL

3 Likes
5

Hi @JoaoMonteiro, did the response provided by Antriksh help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

1 Like