Using rank and Top N

[Anonymous]

Hi Everyone,

I'm trying to use TOP N feauture and RANKX function to filter a visual. TOP N is currently used to filter the years (to only show recent 2 year, ex. 2018-2019). I need a RANKX function to filter out top 20 purchase by customer, but it seems like bot of them at the same time is not working dynamically. So, does anyone have any solution for this? 

Thanks,

[Cmcmahan]

Can you share your current measures?  It's hard to give you any sort of coherent response without knowing basics like how your data model is set up, or what you're currently trying that's failing.

The best thing you could share is the .pbix file you're working with, since that will just give us all the info from the start.

[Anonymous]

I don't have any calculated measures on it. My rows are Company name, address, city, state, etc. Columns are fiscal year, fiscal qtr, and month. Values is number of sold products. I'm applying top N visual filter to the company name by sold products field to see the top 20 company at purchases, and I need another top N visual filter to fiscal year, so I can use a date field to say "Show earliest top 2 years". Since Power Bi doesn't allow using 2 top Ns on the same visual. I was thinking to do RANKX and TOPN but couldn't do it as well. By the way, everything is in one table there is no relationship.

I'm not able to take screenshots or share the .pbix file. It's a company policy. Thank you!

[Cmcmahan]

I'm assuming from your description that you're using a matrix visual?  This data model sounds like a complete mess, which will make any sort of grouping or filtering difficult.  Would it be possible to use a relative date filter for the fiscal year, and then a TOPN for the sold products?  You may want to create a date dimension for this.

If you can't share screenshots or the .pbix file of the actual data per company policy, could you create a mockup with fake/anonymized sample data?  We don't need your actual numbers or whatever proprietary report layout, just data that is in the same data model layout. 

[Anonymous]

Okay I got something. This is exactly what I have. I just have more columns on sales table.

[Cmcmahan]

So is this the final result you want?

It looks like it only has 2018 and 2019 data, and should be filtering only the top 40 companies by Converted_Units, which seemed to be what you were asking for. 

[Anonymous]

This is the result I want to see, but if you can check the visual filters, you'll 2018 and 2019 is selected so it's basically hardcoded. I have a date slicer on the corner so when I go back to 2016 or 2017, it won't show anything. I need the fiscal year to be dynamic as well. So, I thought of doing topN on fiscal year and creating a rank for top 40 companies, but I wasn't able to.