cancel
Showing results for
Did you mean:
Member

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

Re: Ranking TOP N sales without zeros

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.

5 REPLIES 5
Member

Re: Ranking TOP N sales without zeros

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:

Member

Re: Ranking TOP N sales without zeros

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

Sales.pbix

Super User

Re: Ranking TOP N sales without zeros

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.

Member

Re: Ranking TOP N sales without zeros

Many thanks, it worked!

I forgot the criterion of the visual level filter.

Super User

Re: Ranking TOP N sales without zeros

You are welcome.

Announcements

Power BI Super User, Greg Deckler, explains

Power Platform Summit North America

Register by September 5 to save \$200

Back to School Contest

Engage and empower students with Power BI!

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 42 members 897 guests
Recent signins: