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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Using rank and Top N

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,

6 REPLIES 6
Cmcmahan
Resident Rockstar
Resident Rockstar

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
Not applicable

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!

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
Not applicable

Okay I got something. This is exactly what I have. I just have more columns on sales table.1.PNG2.PNG3.PNG4.PNG5.PNG

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
Not applicable

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors