TopN and filtering

DAX DAX Calculations
1

Hi,
I am looking at providing visuals that give
Top 5 customers by Sales this period
Top 5 customers by Sales last period
Bottom 5 customers by Month on month

Top5  Customers (Current Sales) = 
CALCULATE(
                       [Sales],
                       TOPN( 5, 
                       ALL('Customer[Customer Name])
                       ), [Sales], DESC
),
VALUES( 'Customer[Customer Name])

Where this falls short is I want to ignore [customer Name] = “No Name” AND [customer Name] = “”

When I have done the Bottom 5 I have sometimes got 3 results back where the 3 are negative values and then a lot of customers have tied for 4th on zero sales.

How do I avoid these results and always get 5 rows? Been banging head on table and its not helping.
Cheers
E

2

Hi @ells,

Give this a try

Top5 Customers( Current Sales ) =
CALCULATE( [Sales],
    TOPN( 5,
       FILTER( 
           ALL('Customer[Customer Name] ), 
           [customer Name] <> "No Name" && [customer Name] <> "" 
       ), [Sales], DESC 
   ), 
   VALUES('Customer[Customer Name] )
)

If you still have problems please provide a small sample PBIX, thanks
I hope this is helpful

3

@Melissa,
Thanks I was just about to update similar (had not got the logical and)

Does TopN have an issue on tieing on values as some of my Bottom 5 only dispaly 1 value?

Thanks
E

4

Well we shouldn’t use AND but OR come to think of it…

Top5 Customers( Current Sales ) =
CALCULATE( [Sales],
    TOPN( 5,
       FILTER( 
           ALL('Customer[Customer Name] ), 
           [customer Name] <> "No Name" || [customer Name] <> "" 
       ), [Sales], DESC 
   ), 
   VALUES('Customer[Customer Name] )
)
5

Hi @ells, please be sure to check out our DAX Clean Up tool it’s a great way to ensure to make your DAX code easy to read. Thanks!

https://analysthub.enterprisedna.co/dax-clean-up