Forum Discussion

Resolver IV

8 years ago

Solved

Ranking TOP N sales without zeros

I'm doing the sales rank, but when I set up the table for presentation the products that had sales with zero appear in the rank table, how do I mount the rank without the products with sales with ...

Need Help

Ashish_Mathur
8 years ago
Hi Rfranca,

Try this measure in the Mx_sales table

=RANKX(FILTER(All('PRODUCT'[ProductName]),[Ttotal_Sales]>0),[Ttotal_Sales],,ASC,Skip)

In the Visual level filters, apply a criteria on the Ttoal_Sales of >0

Hope this helps.

ccakjcrx

Resolver I

8 years ago

Hey Rfranca!

Try something like this:

MsrRankNo0 = 
IF(
    CALCULATE(SUM(Sheet1[Sales])) <> 0,
    RANKX(ALL(Sheet1),CALCULATE(SUM(Sheet1[Sales])),,1,Skip) - 1
)

You have to subtract 1 to get the rank to start at 1. The calculate expressions are necessary; otherwise, you will get 1s for your ranks. Here is how that turned out on my test data:

Rfranca
Resolver IV
8 years ago

hi, ccakjcrx gooranga1 MarcelBeug vanessa

Unfortunately it did not work out.
I am sending the file for better understanding.

Sales.pbix

Thank you for your help.
- Ashish_Mathur
  Super User
  8 years ago
  Hi Rfranca,
  
  Try this measure in the Mx_sales table
  
  =RANKX(FILTER(All('PRODUCT'[ProductName]),[Ttotal_Sales]>0),[Ttotal_Sales],,ASC,Skip)
  
  In the Visual level filters, apply a criteria on the Ttoal_Sales of >0
  
  Hope this helps.
  - Rfranca
    Resolver IV
    8 years ago
    hi, Ashish_Mathur
    
    Many thanks, it worked!
    I forgot the criterion of the visual level filter.