Ranking TOP N sales without zeros

[Rfranca]

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 zero.

[ccakjcrx]

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]

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 · Accepted solution]

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]

hi, @Ashish_Mathur

Many thanks, it worked!

I forgot the criterion of the visual level filter.

[Ashish_Mathur]

You are welcome.

[siroopm2020]

how to avoid zero values while ranking?

[Ashish_Mathur]

Hi,

That is what my formula suggested on Jan 18, 2018 should do.  If it is not working as expected, then share some data, describe the question and show the expected result.