Graphed LogoGraphed
Login

How to Use Top N Filter in Power BI

Cody Schneider•

Showing a massive table filled with every single product, salesperson, or marketing campaign can quickly overwhelm your audience. To create a report that's actually useful, you need to focus on what matters most. This is exactly where Power BI’s Top N filter shines, allowing you to spotlight your top performers - or your bottom performers - with just a few clicks. In this guide, we'll walk you through how to use the standard Top N filter and then take it a step further by creating a dynamic filter that lets your users decide what "N" is.

What is a Top N Filter?

A Top N filter is a simple but powerful feature that lets you limit the data shown in a visual to a specific number of top- or bottom-ranking items based on a particular metric. The "N" just stands for the number you want to show.

Think of these common business questions:

  • Which were our top 10 selling products last quarter?

  • Who are our top 5 salespeople by revenue?

  • Which 3 social media campaigns have the lowest cost per click?

  • What are the bottom 5 performing landing pages by conversion rate?

In all these cases, a Top N filter provides the answer directly in your visual, cutting through the noise and making your report instantly more insightful.

How to Apply a Basic Top N Filter in Power BI

Let's start with the most straightforward method. We'll build a simple bar chart showing sales by product and filter it to only show the top 5 products.

Step 1: Create Your Visual

First, get a basic visual on your Power BI canvas. For this example, let's use a clustered bar chart.

  1. Select the Clustered bar chart icon from the Visualizations pane.

  2. Imagine you have a Products table with Product Name and a Sales table with a Total Sales measure.

  3. Drag Product Name to the Y-axis field.

  4. Drag Total Sales to the X-axis field.

You should now see a bar chart showing every single product, likely creating a very long, hard-to-read list. Now, let's filter it.

Step 2: Open the Top N Filter Settings

With your visual selected, look at the Filters pane, which is typically to the right of the Visualizations pane.

  1. Find the Product Name field under the "Filters on this visual" section.

  2. Click the small arrow to expand its options.

  3. You'll see a section called Filter type. Click the dropdown that currently says "Basic filtering."

  4. Select Top N from the list.

Step 3: Configure the Filter

Now you'll see the Top N filter configuration options appear.

  • Show items: Here, you can choose either Top or Bottom. Let's stick with Top.

  • Number box: Enter the number you want to show. Type 5 in this box.

  • By value: This is the most crucial step. You need to tell Power BI how to rank the products. Drag your Total Sales measure from the Data pane into the "By value" well.

Step 4: Apply the Filter

Click the Apply filter button at the bottom of the field card.

Instantly, your cluttered bar chart will update to show only the 5 products with the highest Total Sales. Congratulations, you've successfully applied a Top N filter!

Making it Interactive: The Dynamic Top N Filter

A static Top 5 is great, but what if your marketing manager wants to see the top 3, while the sales director wants to see the top 10? Creating different visuals for each request isn't efficient. Instead, you can empower them to choose the "N" themselves using a slicer. This is achieved using a combination of a Power BI Parameter and a simple DAX measure.

Step 1: Create a "What if" Parameter

A parameter lets you create a "what-if" variable that users can interact with. In our case, the variable will be the number of top products to display.

  1. Go to the Modeling tab in the ribbon at the top of Power BI.

  2. Click on New parameter and select Numeric range.

  3. A configuration window will pop up. Let's fill it out:

    • Parameter Name: Let's call it Top N Selection.

    • Data type: Whole number.

    • Minimum: Set it to 1.

    • Maximum: Let's say 20. This will be the highest N a user can select.

    • Increment: 1.

    • Default: 5.

  4. Ensure that "Add slicer to this page" is checked, then Click OK.

Power BI works its magic and does two things for you: it adds a new slicer to your report page for the Top N Selection, and it creates a new calculated table with a corresponding measure in your Data pane called Top N Selection Value.

Step 2: Write a DAX Measure for Ranking

Now, we need to create a measure that works with our new parameter. This measure will rank each product by sales and then check if its rank is within the number selected on the slicer.

  1. Go to the Home tab and click New Measure (or right-click your Sales table and select New measure).

  2. The DAX formula bar will appear. Enter the following code:

Top N Filter Measure =VAR TopNValue = [Top N Selection Value]VAR CurrentRank = RANKX(ALLSELECTED('Products'[Product Name]), [Total Sales],, DESC)RETURNIF(CurrentRank <= TopNValue, 1, 0)

Breaking Down the DAX:

  • TopNValue: This variable simply stores the number currently selected in the slicer (e.g., 5, 10, etc.).

  • CurrentRank: This is the core logic. RANKX is a DAX function that calculates a rank. We told it to rank all selected (ALLSELECTED) product names based on their Total Sales in descending (DESC) order. Using ALLSELECTED is important because it ensures the ranking respects any other filters or slicers on the page.

  • RETURN: The final part of the measure checks if a product's rank is less than or equal to the selected slicer value. If it is, the measure returns a 1. If not, it returns a 0.

Step 3: Apply the New Measure to Your Visual Filter

Finally, we need to connect our new DAX measure to the bar chart to make it dynamic.

  1. First, select your bar chart and go to the Filters pane. Remove the basic Top N filter we created earlier by clicking the 'X' on that filter card. Your chart will go back to showing all products.

  2. Now, look at your Data pane and find the Top N Filter Measure you just created.

  3. Drag Top N Filter Measure into the "Filters on this visual" well.

  4. Expand its card, select "is" and type 1 into the box.

  5. Click Apply filter.

That's it! Now your bar chart is controlled by the slicer. Click and drag the slicer, and you'll see the chart update in real time to show the top 1, top 7, or top 15 products - whatever you choose.

Best Practices and Common Pitfalls

To get the most out of your filters, keep these simple pointers in mind.

Handling Ties in Ranking

What if two products have the exact same sales? The RANKX function has an optional argument to handle ties. The default is Skip, which creates gaps in ranking (e.g., 1, 2, 2, 4). You can also use Dense, which avoids gaps (e.g., 1, 2, 2, 3). For most Top N visuals, the difference is minor, but Dense is often a safer choice for cleaner logic.

The "Others" Category

Sometimes, showing only the top performers isn't enough, you also want to see how much "everyone else" contributed. Creating a dynamic "Others" category involves more advanced DAX, where you group all the products that are not in the Top N into a single bar. While beyond this basic tutorial, it's a fantastic technique for reports that need complete context.

Performance on Large Datasets

While DAX is highly optimized, using complex ranking measures on datasets with millions of rows can sometimes impact report performance. Always test your dynamic Top N filters on your actual dataset to ensure the user experience is smooth and responsive.

Communicate a Clear Title

To avoid confusion, make your chart's title dynamic, too. You can create another simple measure like Dynamic Chart Title = "Showing Top " & [Top N Selection Value] & " Products by Sales and use that measure to power your visual's title. This helps users immediately understand what they are looking at.

Final Thoughts

You've now learned how to create both a simple Top N filter and a more flexible, user-driven Top N visual in Power BI. Mastering this technique takes your reports from being noisy data dumps to focused, high-impact dashboards that guide decision-making by highlighting the information that truly counts.

While Power BI is an incredibly powerful platform for this kind of detailed analysis, it requires learning the interface, understanding data models, and writing DAX. At Graphed we handle this complexity for you. Instead of navigating menus and writing formulas to find your top performers, you can simply ask, "Show me a bar chart of my top 10 products by revenue this year." Our AI assistant instantly builds a live, interactive visualization for you, connecting all your data sources so you can get from question to insight in seconds, not hours.