cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LUCASM Regular Visitor
Regular Visitor

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?

4 REPLIES 4
Super User
Super User

Re: Bottom 5

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




LUCASM Regular Visitor
Regular Visitor

Re: Bottom 5

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

Super User
Super User

Re: Bottom 5

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




LUCASM Regular Visitor
Regular Visitor

Re: Bottom 5

Hi @MFelix 

 

Thanks for your help.

I tried your approach, but that does not work for me either.....

I shall have to wait and see if anything happens from your question on the errors forum.

 

Kind Regards

 

Martin

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (5,298)