How to Do Top N in Power BI

Showing your top performers is one of the most common tasks in any report - whether it’s your top 10 best-selling products, top 5 highest-performing ad campaigns, or top 3 sales reps. Power BI makes this simple, offering a few different ways to create "Top N" reports, from a quick filter to a fully interactive slicer. This article will walk you through three practical methods to master this essential skill.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What is a Top N Filter and Why You'll Use It Constantly

A "Top N" filter simply isolates the top number ("N") of items in a category based on a specific value. Think of it as a dynamic leaderboard for your data.

Instead of showing a long list of 100+ products that clutters your report, you can zero in on the handful that truly matter. It immediately answers questions like:

• Which marketing channels are driving the most conversions?
• Who are our most valuable customers by purchase volume?
• Which landing pages receive the most traffic?

By filtering down to the top performers, you make your reports cleaner, more insightful, and easier for your team to understand and act on. It helps focus everyone's attention on what's working best.

Method 1: The Quick and Easy Way with the Filter Pane

The simplest way to create a Top N view is by using Power BI's built-in "Filters" pane. This method is perfect for static reports where you don't need the user to change the "N" value (e.g., the report should always show the Top 5). Let's build one for finding the Top 5 products by sales revenue.

Step-by-Step Guide

Imagine you have a simple data model with a Sales table (containing Sales Amount) and a Products table (containing Product Name).

1. Create a Basic Visual: First, create a visualization that shows all your products and their performance. A clustered bar chart is a good choice. Drag Product Name onto the Y-axis and Sales Amount onto the X-axis.

You can see it's a long, scrolling list - not very easy to read.

2. Open the Filters Pane: With your visual selected, look at the Filters pane on the right. You'll see filters "On this visual," "On this page," and "On all pages." We just want to filter our bar chart, so we’ll stick to the "On this visual" section.

3. Add the Filtering Field: Drag the field that has the items you want to rank - in our case, Product Name - from the Data pane into the "Filters on this visual" well.

4. Change the Filter Type to "Top N": Click the "Filter type" dropdown and change it from "Basic filtering" to Top N.

5. Configure the Top N Filter: New options will now appear:

• In the Show items section, keep "Top" selected and type 5 into the box. (You could also choose "Bottom" to find your worst performers).
• Drag the metric that defines "top" - our Sales Amount - into the By value box.

6. Apply the Filter: Click the "Apply filter" button. And just like that, your chart instantly updates to show only the top 5 products by sales amount.

This is a fast and effective method for static analysis, but what if you want your team to be able to choose if they see the Top 3, Top 5, or Top 10?

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Method 2: Creating a Dynamic Top N Slicer with DAX

For more interactive reports, you can let users choose their own "N" value using a slicer. This empowers your colleagues to explore the data themselves. It requires a pinch of DAX (Data Analysis Expressions), but don't worry - we'll break it down step-by-step.

Our goal is to create a slicer that lets a user select a number, and our chart will instantly react to show that many top products.

Step 1: Create a Numeric Range Parameter

First, we need to generate a list of numbers for our slicer.

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

Power BI does two things: it adds a slicer to your report page and creates a new calculated table (Top N Selector) with a companion DAX measure (Top N Selector Value). This measure always returns the single number currently selected on the slicer.

Step 2: Write the Ranking Measure

Next, we need to dynamically rank every single product based on its sales. For this, we'll use the powerful RANKX function. On the Home tab, click New Measure and enter this formula:

Product Rank = 
RANKX(
    ALLSELECTED('Products'[Product Name]),
    CALCULATE(SUM('Sales'[Sales Amount]))
)

Let's quickly translate that formula into plain English:

• RANKX(...): This is the ranking function.
• ALLSELECTED('Products'[Product Name]): This tells RANKX to look at every single product name that is currently visible after any other filters on the page are applied. This is important for making sure the ranking behaves as expected.
• CALCULATE(SUM('Sales'[Sales Amount])): This is the expression we're ranking everything by - our total sales.

In short: "Calculate the sales for each product, and then rank them against each other."

Step 3: Create a "Flag" Measure to Connect Everything

Now we create a final measure that acts as a "flag." It will check if a product's rank is within the number selected on our slicer. If it is, the flag is "on" (returns a 1), if not, it's "off" (returns a 0).

Create another New Measure with this DAX:

Top Products Filter = 
IF(
    [Product Rank] <= [Top N Selector Value],
    1,
    0
)

Here’s the simple logic behind this: "If the product’s calculated rank is less than or equal to the number chosen in our slicer, then return a 1. Otherwise, return a 0."

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 4: Put it All Together in the Visual

We're ready to make our chart interactive. Select the same bar chart from Method 1.

1. If you still have the old Top N filter on it, remove it.
2. From the Data pane, drag our new Top Products Filter measure into the "Filters on this visual" well.
3. Set the "Show items when the value" dropdown to is and type 1 in the box.
4. Click Apply filter.

Now, interact with the slicer you created. As you move the slider, the chart dynamically updates - showing the Top 3, Top 7, Top 15, or whatever you choose! You've successfully built a fully interactive discovery tool.

Method 3: Advanced "Top N + Others" Category

Sometimes you want to highlight the top performers but not completely ignore everyone else. A "Top N + Others" chart is the solution. It lets you display, for example, your Top 5 products as individual slices of a pie chart and then groups all other products into a single slice called "Others." This is an advanced DAX pattern, but it's one of the most useful tricks you can learn.

Step 1: Create a Disconnected "Grouping" Table

The trick to this method is to create an entirely new, separate table in your data model that contains the names of your top performers plus one extra row for "Others." This determines the categories that will show up in your chart.

1. On the Modeling tab, click New table.
2. Enter the following DAX formula. For this example, let's hard-code N as 5.

Product Display = 
VAR TopNValue = 5
VAR TopProducts = 
    CALCULATETABLE(
        VALUES('Products'[Product Name]),
        TOPN(
            TopNValue,
            ALL('Products'[Product Name]),
            CALCULATE(SUM('Sales'[Sales Amount]))
        )
    )
VAR OthersRow = ROW("Product Name Display", "Others")
RETURN
UNION(
    SELECTCOLUMNS(TopProducts, "Product Name Display", [Product Name]),
    OthersRow
)

This may look complex, but it logically does three things:

• VAR TopProducts: It first calculates total sales for every single product, finds the names of the Top 5 products, and stores this list in a variable.
• VAR OthersRow: It creates a single row with the text "Others".
• RETURN UNION(...): It combines (unions) the list of top product names with the "Others" row into a final, two-column table called Product Display.

Remember: this new table is not related to any other tables in your model. It exists solely to create the labels on our chart's axis.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 2: Create the Calculation Measure

Now we need a measure that calculates the sales value for each of these new "groups" - either for an individual top product or for the collective "Others."

Create a New Measure:

Sales for Top + Others = 
VAR TopNValue = 5
VAR SelectedDisplayProduct = SELECTEDVALUE('Product Display'[Product Name Display])
VAR AllSales = CALCULATE(SUM('Sales'[Sales Amount]), ALL('Products'))
VAR TopProductNames =
    CALCULATETABLE(
        VALUES('Products'[Product Name]),
        TOPN(
            TopNValue,
            ALL('Products'[Product Name]),
            CALCULATE(SUM('Sales'[Sales Amount]))
        )
    )
VAR SalesForTopProductsSeparately = CALCULATE(SUM('Sales'[Sales Amount]), KEEPFILTERS('Products'[Product Name] IN TopProductNames ))
VAR OtherProductsSales = AllSales - SalesForTopProductsSeparately
RETURN 
IF(
    SelectedDisplayProduct = "Others",
    OtherProductsSales,
    CALCULATE(SUM('Sales'[Sales Amount]),'Products'[Product Name] = SelectedDisplayProduct)
)

This is a dense formula, but here's the core idea: it checks what category from our new Product Display table the visual is currently looking at. If it’s seeing a Top product name (e.g., "Product A"), it calculates sales for only that product. If it’s seeing "Others", it calculates total sales and subtracts the sales of all the top products, leaving just the remainder.

Step 3: Build the "Top N + Others" Chart

Now, let's build a pie chart with our new assets.

1. Create a new Pie chart visual.
2. From the new Product Display table, drag Product Name Display into the Legend field.
3. From your measures, drag the new Sales for Top + Others measure into the Values field.

Instantly, you get a clean and focused chart showing your top 5 products, with everything else neatly bucketed together. This is a highly professional report that keeps your audience focused on the signal, not the noise.

Final Thoughts

Mastering the Top N calculation is a fundamental skill that transforms cluttered reports into focused insights. We've covered the quick static method using the Filters Pane, the interactive user-driven approach with a slicer, and the advanced "Top N + Others" pattern for ultimate clarity.

DAX can be incredibly powerful, but as you see, it sometimes takes several complex steps to get the insights you need. At Graphed, we've built a way to sidestep this complexity completely. Instead of writing formulas and configuring filters yourself, you just connect your sales and marketing data, and ask questions in plain English like: "Show me a bar chart of my top 5 products by sales" or "create a pie chart of our top 10 marketing campaigns from Google Analytics and group the rest as Others." The AI handles building the live, interactive visualizations for you in seconds. If you're ready to get answers without the friction, you can sign up for Graphed for free.