How to Use TOPN Function in Power BI

Trying to show just your top-performing products or best sales reps in a Power BI report can sometimes feel like a chore. The TOPN function in DAX is the perfect tool for the job, letting you filter your tables to spotlight the most important data. This article guides you through how to use the TOPN function, from basic syntax to practical examples you can start using today.

What is the TOPN Function in Power BI?

In simple terms, the TOPN function returns a table that includes a specific number of top rows from another table, based on a value you tell it to rank by. Think of it as a bouncer for your data, only letting the "Top 5" or "Top 10" entries into your chart or calculation.

This is useful when you want to answer questions like:

What are my top 10 best-selling products this quarter?
Which 5 marketing campaigns are driving the most traffic?
Who are our 3 most productive sales representatives based on closed deals?

Without a function like TOPN, your reports can get cluttered with hundreds of rows, making it hard to see who or what is actually driving results. TOPN helps you zero in on the a-listers of your data.

Understanding the TOPN Syntax

At its core, TOPN is a DAX function that returns a table. Like any function, it has its own specific structure, or syntax, that you need to follow. Let's break it down piece by piece.

Syntax Breakdown:

<n_value>,: This is simply the number of top rows you want. If you want your top 10 products, you’d put 10 here. This has to be a single, constant number.
<table>,: This is the table from which the rows are returned. It can be a physical table in your model (like 'Sales') or a virtual table created by another function (like VALUES or SUMMARIZE). This is the part that often trips people up, and we'll cover it in our examples.
<orderBy_expression>,: This is the heart of the function. It's the calculation that Power BI will use to rank the rows in your table. This is usually a measure, like SUM(Sales[Revenue]) or COUNT(Customers[CustomerID]).
[<order>]: This part is optional. You can specify whether to sort results in ASC (ascending order, from lowest to highest) or DESC (descending order, from highest to lowest). Since we're usually interested in the top performers, DESC is the default and most commonly used option.

Two Ways to Use TOPN: Calculated Tables vs. Measures

You can use the TOPN function in two primary ways: to create a new, static table in your data model (a Calculated Table) or to create a dynamic, reusable calculation (a Measure). Both are useful, but they solve different problems.

Method 1: Creating a "Top N" Calculated Table

A calculated table is a new table you add to your model based on a DAX formula. It’s calculated when you create it or refresh the data. This is great when you need a separate, filtered list of items to work with.

Scenario: You are a marketing manager for an e-commerce store and want to create a permanent table that only contains your top 10 best-selling products of all time. You can use this table to create specific visuals or relationships in your model.

Step-by-Step Instructions:

From the Power BI Desktop, navigate to the Data view (the grid icon on the left).
In the "Home" or "Table tools" ribbon at the top, click on New table.
The formula bar will appear. Enter the following DAX formula and press Enter:

How this formula works:

10 tells the function we want the top 10 products.
SUMMARIZE('Sales', 'Products'[Product Name]) creates a summary table that lists all unique product names that appear in the 'Sales' table. This is our <table> argument. You could also use VALUES('Products'[Product Name]) here for a similar result.
CALCULATE( SUM('Sales'[Total Revenue]) ) is our <orderBy_expression>. It tells TOPN to rank the products based on the sum of their 'Total Revenue'. The CALCULATE function ensures the sales calculation happens correctly for each product.
DESC sorts the products from highest revenue to lowest.

After running this, you'll see a new table named "Top 10 Products by Sales" in your fields pane. It's a simple, two-column table listing only your top performers.

Method 2: Using TOPN In a Dynamic Measure

This is often the more powerful and flexible approach. A measure doesn’t store any data itself, it performs a calculation on the fly based on the context of your report (like slicers, filters, and visuals).

Scenario: As the same marketing manager, you don't just want a static list. You want to see the total revenue generated only by your top 5 products, and you want that number to change dynamically if you filter by country or timeframe.

Step-by-Step Instructions:

Navigate back to the Report view (the bar chart icon on the left).
Right-click on your 'Sales' table in the Fields pane and select New measure.
In the formula bar, enter this DAX code:

How this formula works:

This formula might look complex, so let's walk through it. It's a classic CALCULATE pattern where a table function is used as a filter.

The outer CALCULATE function modifies the filter context. It is asked to calculate the total revenue.
The second part of the CALCULATE serves as the filter. Here, our filter is the list of top 5 products returned by the TOPN function.
The TOPN function works just like before: it identifies the top 5 products based on revenue.
Crucially, the result of TOPN here is not a visible table, it's a virtual table that is fed directly into CALCULATE. In effect, we are telling Power BI: "First, find the Top 5 products by revenue, then and only for those 5 products, calculate the total revenue."

Now, you can put this new "Sales From Top 5 Products" measure into a card visual next to a "Total Sales" measure. If you add a slicer for ‘Year’, you'll see both numbers update. The "Total Sales" will show sales for the selected year, and "Sales From Top 5 Products" will show sales for the top 5 products within that specific year. That’s the dynamic power of using TOPN in a measure.

Beyond the Basics: Other TOPN Scenarios

Once you're comfortable with the basics, TOPN can be adapted for more complex analyses.

Finding the 'Bottom N' Performers

What if you want to find your worst-performing products to see if they should be discontinued? Simply change the last argument of TOPN from DESC to ASC (ascending).

This will find the 5 products with the lowest 'Total Revenue' and sum their sales.

Combining With Other Filters

What if you want to find the top 5 products specifically for online sales? You can add another filter condition inside your CALCULATE function.

Here, the KEEPFILTERS function ensures that our "Online" channel filter is combined with the TOPN filter, giving us the revenue from the top 5 products sold through the online channel.

Quick Alternative: The "Top N" Visual Filter

Don’t want to write DAX? Power BI has a built-in filter that does the trick for simple-use cases, but it's less flexible. It applies only to a single visual, not a reusable measure.

How to Use It:

Create a visual, like a table or bar chart, showing 'Product Name' and 'Total Revenue'.
With the visual selected, go to the Filters pane. You'll see 'Product Name' listed as a filter for this visual.
Expand the 'Product Name' filter card, and from the "Filter type" dropdown, select Top N.
You'll get new options:
- Show items: Select Top and type 10 in the box.
- By value: Drag your 'Total Revenue' measure into this field.
Click Apply filter.

Your visual will instantly update to show only the top 10 products. This is fantastic for quick analysis on a single chart, but if you need to perform calculations with that top 10 list (like finding their contribution to total sales), you’ll need the DAX TOPN function.

Final Thoughts

The TOPN function is an essential tool in any Power BI user's toolkit. It cleans up cluttered reports by letting you focus on the most impactful data points, whether you use it to create a new calculated table or a powerful dynamic measure. A solid grasp of this function will help you build cleaner, more insightful dashboards.

While mastering DAX functions like TOPN is a great skill, sometimes you need to find your top performers without writing a single line of code. We built Graphed to democratize data analysis. Connect your data sources like Google Analytics, Shopify, or your CRM in just a few clicks. Then, simply ask questions in plain English like, "show me my top 10 products by revenue last quarter as a bar chart," and get a live, interactive visualization in seconds.