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.

Practice

1. What does the TOPN function do in Power BI?

easy

A. Joins two tables based on a key column.

B. Calculates the sum of a column in a table.

C. Filters rows where a condition is true.

D. Returns the top N rows from a table based on a specified column's values.

Solution

1. Step 1: Understand the purpose of TOPN

   TOPN is designed to return a subset of rows from a table, specifically the top N rows sorted by a column.

2. Step 2: Compare with other functions

   Functions like SUM aggregate values, FILTER filters rows by condition, and JOIN merges tables. TOPN specifically returns top rows.

3. Final Answer:

   Returns the top N rows from a table based on a specified column's values. -> Option D

4. Quick Check:

   TOPN = returns top rows [OK]

Hint: TOPN picks top rows by sorting a column [OK]

Common Mistakes:

• Confusing TOPN with aggregation functions like SUM
• Thinking TOPN filters by condition instead of top values
• Mixing TOPN with table joins

2. Which of the following is the correct syntax for the TOPN function in DAX?

easy

A. TOPN(<number_of_rows>, <table>, <order_by_column>, <order>)

B. TOPN(<table>, <number_of_rows>, <order_by_column>, <order>)

C. TOPN(<number_of_rows>, <order_by_column>, <table>, <order>)

D. TOPN(<order_by_column>, <number_of_rows>, <table>, <order>)

Solution

1. Step 1: Recall TOPN function syntax

   The correct syntax is TOPN(number_of_rows, table, order_by_column, order). The first argument is how many rows to return.

2. Step 2: Verify argument order

   Number of rows comes first, then the table, then the column to sort by, and finally the sort order (ASC or DESC).

3. Final Answer:

   TOPN(<number_of_rows>, <table>, <order_by_column>, <order>) -> Option A

4. Quick Check:

   Syntax = TOPN(number_of_rows, table, column, order) [OK]

Hint: Number of rows is always first in TOPN syntax [OK]

Common Mistakes:

• Swapping table and number_of_rows arguments
• Placing order_by_column before table
• Omitting the order argument

3. Given the table Sales with columns Product and Revenue, what does this DAX expression return?

TOPN(2, Sales, Sales[Revenue], DESC)

medium

A. The two products with the highest revenue.

B. The two products with the lowest revenue.

C. All products sorted by revenue descending.

D. An error because TOPN needs ASC order.

Solution

1. Step 1: Analyze the TOPN parameters

   TOPN(2, Sales, Sales[Revenue], DESC) means return 2 rows from Sales table sorted by Revenue in descending order.

2. Step 2: Understand sorting order effect

   Descending order means highest values come first, so it returns top 2 products with highest revenue.

3. Final Answer:

   The two products with the highest revenue. -> Option A

4. Quick Check:

   TOPN with DESC = top highest values [OK]

Hint: DESC means highest values first in TOPN [OK]

Common Mistakes:

• Confusing DESC with ASC order
• Thinking TOPN returns all rows sorted
• Assuming TOPN returns lowest values by default

4. Identify the error in this DAX expression:

TOPN(3, Sales)

medium

A. Table name should be in quotes.

B. The number of rows should be last argument.

C. Missing the order_by_column argument.

D. Revenue column cannot be used in TOPN.

Solution

1. Step 1: Check TOPN function arguments

   TOPN requires (at minimum) three arguments: number_of_rows, table, order_by_column [, order (ASC or DESC)].

2. Step 2: Identify missing argument

   The expression lacks the order_by_column argument, which is mandatory to specify the column for sorting.

3. Final Answer:

   Missing the order_by_column argument. -> Option C

4. Quick Check:

   TOPN needs order_by_column [OK]

Hint: Always include order_by_column in TOPN [OK]

Common Mistakes:

• Omitting order_by_column argument
• Putting number_of_rows last
• Using quotes around table names

5. You want to create a measure that shows the total revenue of the top 3 products by revenue. Which DAX expression correctly uses TOPN to achieve this?

hard

A. CALCULATE(SUM(Sales[Revenue]), TOPN(3, Sales, DESC))

B. SUMX(TOPN(3, Sales, Sales[Revenue], DESC), Sales[Revenue])

C. SUMX(FILTER(Sales, Sales[Revenue] >= TOPN(3, Sales, Sales[Revenue], DESC)), Sales[Revenue])

D. CALCULATE(TOPN(3, Sales, Sales[Revenue], DESC), SUM(Sales[Revenue]))

Solution

1. Step 1: Understand the goal

   You want to sum revenue only for the top 3 products by revenue.

2. Step 2: Analyze each option

   CALCULATE(SUM(Sales[Revenue]), TOPN(3, Sales, DESC)) has invalid TOPN syntax (missing order_by_column). SUMX(TOPN(3, Sales, Sales[Revenue], DESC), Sales[Revenue]) uses SUMX over TOPN table, correctly summing revenue of top 3 rows. SUMX(FILTER(Sales, Sales[Revenue] >= TOPN(3, Sales, Sales[Revenue], DESC)), Sales[Revenue]) misuses FILTER with TOPN inside condition, which is invalid. CALCULATE(TOPN(3, Sales, Sales[Revenue], DESC), SUM(Sales[Revenue])) misplaces SUM inside CALCULATE with TOPN, causing syntax error.

3. Final Answer:

   SUMX(TOPN(3, Sales, Sales[Revenue], DESC), Sales[Revenue]) -> Option B

4. Quick Check:

   SUMX over TOPN table sums top rows [OK]

Hint: Use SUMX over TOPN to sum top rows [OK]

Common Mistakes:

• Using TOPN directly inside CALCULATE filter
• Misusing FILTER with TOPN in condition
• Putting aggregation inside CALCULATE incorrectly