Forum Discussion

LUCASM's avatar
LUCASM
Icon for Helper IV rankHelper IV
7 years ago

Bottom 5

I am trying to build a Matrix table that shows the bottom 5 SKU's sold last month.

I have seen this link https://community.powerbi.com/t5/Desktop/Bottom-N-without-blanks-and-zeros/m-p/545670#M256467

but it does not work for me it is not possible to have two TOP N filters.

 

I currently have myself at Barcode in Rows and Revenue in Values

My Filters are 

Barcode bottom 5 by Revenue

Revenue is greater than

Revenue is not blank

 

This only shows the bottom 3, if I replace the Revenue is greater than with Revenue is greater than or equal to 0 or remove it completely I get the bottom 5 but this is not any good, I need top omit any zero values.

 

How can I do this?

Need Help

4 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    7 years ago

    Hi LUCASM ,

     

    Are you sure that your top 5 above zero are 5 and not 3?

     

    Can you share a sample of your file?

     

    Regards,

    MFelix

    • LUCASM's avatar
      LUCASM
      Icon for Helper IV rankHelper IV
      7 years ago

      Hi MFelix

       

      Quite sure my total dataset contains 53 different SKUs

      11 had zero sales

      the rest have sales between 22 Euro and 4 million euro

       

      An example pibx file here.

       

      Martin

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        7 years ago

        Hi LUCASM ,

         

        Not really sure what is happening because if you place the sales filter on the report level the bottom 5 works as expected however on visual level it's not working properly.

         

        One way to overcome this is to create the following measure to use on your bottom 5 (place it on the bar code TOPN instead of sales)

        Sales Positive = CALCULATE(SUM(Sheet1[Sales]); Sheet1[Sales] > 0)

        I will put a question on the errors forum.

         

        Regards,

        MFelix

        Hope this helps