How to Create Top N Slicer in Power BI

Building an interactive "Top N" filter in Power BI allows your users to dynamically change a report from showing the top 5 products to the top 10, or top 3, all with a single click. This simple slicer adds a powerful layer of user-driven exploration to any dashboard. This tutorial will walk you through exactly how to create a flexible Top N slicer using parameters and a few lines of DAX.

What is a Top N Slicer and Why Use One?

A "Top N" filter, quite simply, shows you the top-performing items in a list based on a specific business metric. For example, you might want to see your:

• Top 10 selling products
• Top 5 sales representatives by revenue
• Top 3 marketing campaigns by lead generation

While you can use Power BI's built-in filter pane to show a static top 10 list, that number is fixed. The report viewer can't change it. A Top N slicer, on the other hand, puts that control directly into the hands of your audience. They can use a slider or input box to instantly change the "N" value, making the report dynamic and much more insightful. This lets team members, clients, or executives answer their own follow-up questions without needing to ask for a new version of the report.

The Two Main Ways to Create a Top N Filter

There are two primary methods for this in Power BI. Understanding them helps you choose the right one for your reporting needs:

1. The "What If" Parameter Slicer (Most Flexible): This method creates a highly interactive slider that users can adjust. It’s perfect for dashboards where you want to encourage exploration and self-service analysis. We'll spend the most time on this method as it offers the best user experience.
2. The Filters Pane (Static & Simple): This is a quick way to apply a fixed top-whatever filter to a specific visual. It’s useful when you, the report creator, know you only want to show the Top 5, and users don't need to change it. We'll cover this as a faster alternative.

Method 1: Creating a Dynamic Slicer with a "What If" Parameter

This is the gold standard for creating an interactive Top N slicer. We will accomplish this in four main steps: creating a parameter for user input, ranking our items with a DAX measure, creating a second measure to connect the ranking to the user input, and finally, applying it all as a visual filter.

For this example, let's assume we have a simple dataset with Product Name and Total Sales.

Step 1: Create a "What If" Parameter

First, we need to create the slicer itself that users will interact with. Power BI's "What If" Parameter feature makes this easy.

1. Navigate to the Modeling tab in the Power BI ribbon.
2. Click on New parameter and select Numeric range.
3. A configuration window will pop up. Let's fill it out:
4. Make sure "Add slicer to this page" is checked, then click Create.

Power BI has now done two things in the background: it created a slicer on your report canvas and, more importantly, it generated a brand-new calculated table (named "Top Products Slicer") with its own column and a measure. If you click on the "Data" view, you'll see this table. The key takeaway is the measure it created, which is usually named 'Top Products Slicer'[Top Products Slicer Value]. This measure will always hold the current number selected on the slider.

Step 2: Create a DAX Measure to Rank Your Items

Next, we need to rank our products based on sales so we know which ones are #1, #2, #3, and so on. We'll do this using a DAX measure and the RANKX function.

1. Right-click on your main data table (e.g., 'Sales Data') and select New measure.
2. Enter the following DAX formula:

Product Rank = 
RANKX(
    ALLSELECTED('Sales Data'[Product Name]),
    [Total Sales],
    ,
    DESC,
    Dense
)

Breaking Down the Formula:

• RANKX: This is the ranking function in DAX.
• ALLSELECTED('Sales Data'[Product Name]): This table expression tells Power BI to respect current filters but rank only among selected products.
• [Total Sales]: This is the metric we're ranking by. Replace this with your own measure if needed.
• DESC: Rank the highest sales as #1.
• Dense: Ensures that if two products tie, the next rank skips accordingly without gaps.

Step 3: Create a Final DAX Measure to Filter the Visual

Now we need to connect our rank measure to our slicer. We'll create a final "flagging" measure that returns a 1 if a product's rank falls within the slicer's selected number, and a 0 if it doesn't.

1. Right-click your main data table again and select New measure.
2. Enter this DAX formula:

Top N Filter = 
VAR SelectedN = [Top Products Slicer Value]
VAR ProductRank = [Product Rank]
RETURN
IF(
    ProductRank <= SelectedN,
    1,
    0
)

Breaking Down the Formula:

• SelectedN: Stores the current value from the slicer (the number of top products to show).
• ProductRank: The rank of each product.
• IF: Checks if the product rank is less than or equal to the selected number, returns 1 if true, 0 otherwise.

Step 4: Apply the Filter to Your Visual

This is the final step to make your visual respect the Top N filter.

1. Select the table or chart visual you want to filter.
2. Open the Filters pane.
3. Drag the Top N Filter measure into the "Filters on this visual" area.
4. Set the filter condition: Show items when the value is is 1.
5. Click Apply filter.

Your chart will now update dynamically based on the slicer value! Using the slicer from Step 1, you can control how many top products are displayed in real time.

Method 2: Using the Filters Pane Directly (A Simpler Static Approach)

What if you don't need all that interactivity? If you just want to set up a chart that always shows the Top 5 products and the end-user never needs to adjust it, there's a faster method available directly in the Filters pane.

1. Select the visual you want to filter.
2. In the Filters pane, find the field that represents your category (e.g., Product Name). If it's not already there, drag it into the "Filters on this visual" area.
3. Click the dropdown for "Filter type" and select Top N.
4. Set the following:
5. Click Apply filter.

The visual now only shows the top 5 products by sales. It's quick and effective but lacks the interactive flexibility of the parameter method.

Final Thoughts

In this walkthrough, we saw how to build a fully interactive "Top N" filter in Power BI using a numeric parameter and a few DAX measures. This gives report viewers the power to drill down into the most important data points, making your dashboard an active tool for exploration rather than just a static view.

While building reports with these advanced features in Power BI is a valuable skill, it's often far more time-consuming than it should be. With Graphed, we’ve made connecting to your data and getting answers as easy as asking a question. Instead of writing DAX, you can simply ask, "show me my top 10 products by sales this month," and we instantly create a real-time visualization for you. We help you skip the technical setup and get straight to the insights that matter for growing your business.