Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Rfranca
Resolver IV
Resolver IV

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.

 

Clipboard01.gif

 

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

 

Untitled.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
ccakjcrx
Resolver I
Resolver I

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:

 

Screenshot.jpg

 

 

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.

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.

 

Untitled.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

hi, @Ashish_Mathur

 

Many thanks, it worked!

I forgot the criterion of the visual level filter.

 

Clipboard01.gif

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

how to avoid zero values while ranking?

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.