Forum Discussion

Rfranca's avatar
Rfranca
Icon for Resolver IV rankResolver 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 zero.

 

 

Need Help
  • Ashish_Mathur's avatar
    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.

     

     

7 Replies

  • ccakjcrx's avatar
    ccakjcrx
    Icon for Resolver I rankResolver 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:

     

     

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper 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.