TOPN function in Power BI - Step-by-Step Guide

Introduction

The TOPN function helps you find the top or bottom items in your data based on a number you choose. It is useful when you want to focus on the best or worst performers, like top-selling products or lowest expenses.

When you want to show the top 5 products by sales in a report.

When you need to highlight the top 3 customers with the highest purchases.

When you want to filter a table to only show the top 10 employees by performance score.

When you want to create a chart that displays the top regions by revenue.

When you want to compare the top 7 months with the highest profits.

Steps

Step 1: Open

- Power BI Desktop

The main Power BI interface appears with your data loaded

Step 2: Click

- Modeling tab

Modeling options appear in the ribbon

Step 3: Click

- New measure button

A formula bar opens to write a DAX measure

Step 4: Type

- formula bar

The measure formula is ready to be created

💡 Start typing TOPN to see the function autocomplete

Step 5: Enter

- formula bar

The measure is created and appears in the Fields pane

💡 Example formula: Top5Products = CALCULATETABLE(TOPN(5, Products, Products[Sales], DESC))

Step 6: Drag

- the new measure to a visual like a table or chart

The visual updates to show only the top N items based on your measure

Before vs After

Before

A table visual shows all 100 products with their sales numbers

After

The table visual shows only the top 5 products with the highest sales

Settings Reference

Number of items

📍 Inside the TOPN function as the first argument

Defines how many top items to return

Default: No default, you must specify

Table

📍 Inside the TOPN function as the second argument

The data set from which to select top items

Default: No default, you must specify

Order by column

📍 Inside the TOPN function as the third argument

The column used to rank items

Default: No default, you must specify

Sort order

📍 Inside the TOPN function as the fourth argument

Defines if top means highest (DESC) or lowest (ASC)

Default: DESC

Common Mistakes

Not specifying the sort order argument in TOPN

Without sort order, TOPN may not return the expected top or bottom items

Always include ASC or DESC to clearly define sorting direction

Using TOPN on a column that has duplicate values without a tie-breaker

TOPN may return more items than expected if there are ties

Add a second order by column to break ties and get consistent results

Trying to use TOPN directly in a visual filter without a supporting measure

TOPN returns a table, not a single value, so it cannot be used directly as a filter

Use TOPN inside a measure or calculated table, then use that in your visual

Summary

TOPN helps you find the top or bottom items in your data based on a number.

You must specify how many items, the table, the column to sort by, and the sort order.

Use TOPN inside measures or calculated tables to filter visuals effectively.