Power BI Top N DAX Function [How to Use]

This Power BI tutorial will help you work with the Power BI TOPN DAX function.

I recently worked on a Power BI report where I utilized the Power BI TOPN() function to identify the best-selling day in my dataset easily. This tutorial is perfect if you want to learn more about Power BI TOPN().

In this article, we learn what the Power BI top n function is and how to work with the Power BI top n function. Also, I will tell you how to use the top n function in Power BI measure.

Power BI Top N DAX Function

The “Top N” function in Power BI allows you to filter your data to only show the top (or bottom) N items based on a certain measure or criteria. It returns the top N rows of the specified table.

For example, you might use it to display the top 10 selling products or the bottom five performing salespeople.

Power BI TOP N function syntax:

TOPN(<N_Value>, <Table>, <OrderBy_Expression>, [<Order>[, <OrderBy_Expression>, [<Order>]]…])

Where:

  • N_Value = The number of rows you want to return. It needs to be a number.
  • Table = Name of the Table.
  • OrderBy_Expression = (Optional) Any DAX expression.
  • Order = When the Order parameter is omitted, the default sorting is in descending order, represented by one or TRUE. If you specify 0 (zero) or FALSE, the sorting will ascend.

How to Use Top N Function in Power BI

Now we see how to work with the top n function in Power BI

Here, we have a SharePoint list (US Superstore data) that contains below columns with various data types:

ColumnsData Types
Order DateDate and time
CitySingle line of text
CategorySingle line of text
Sub-CategorySingle line of text
Product NameSingle line of text
SalesCurrency
QuantityNumber
ProfitCurrency
How it works in Power BI TOPN DAX Function

Follow the below steps to see how to work with the Power BI top n function.

1. Open Power BI Desktop and load the data. Then, you see data in the Data pane.

Power BI DAX TOPN function

2. Under the Home tab, click on the New measure.

Understanding the TOPN() DAX Function in Power BI

3. In the formula bar, click the below expression. Then click Commit.

Total Sales = SUM('US Superstore data'[Sales])

Where:

  • Total Sales = Name of the Measure
  • SUM = DAX Function
  • US Superstore data = Name of the Table
  • Sales = Name of the Column
When & How to Use TOPN in Power BI

Now, we create a table using the top n function because we learn the return type of the top n function is a table.

4. Go to the Table view; under the Home tab, click on New table.

Power BI TOPN Function

First, we see the top 1 based on Total Sales.

5. In the formula bar, click the below expression. Then click Commit.

Top 1 Sales = 
TOPN (            
    1,  
    'US Superstore data', 
    [Total Sales] 
)

Where:

  • Top 1 Sales = Name of the new Table
  • 1 = The number of rows we want to return
  • US Superstore data = Name of the Dataset
  • Total Sales = Name of the Measure
TOPN DAX Function How it works in Power BI

6. After clicking Commit, you see one table with one row.

TOPN function in CALCULATE in Power BI

This way, you use the top n function in Power BI

Example – 2 (Top 10 Cities Based on Sales Value)

Now, we see the top 10 city values based on sales value. To do this, follow the below steps:

1. Click on the New table. Then, in the formula bar, put the below expression.

Top 10 cities = 
TOPN (            
    10,  
    VALUES('US Superstore data'[City]), 
    [Total Sales] 
)

Where:

  • Top 10 cities = Name of the new Table
  • 10 = The number of rows we want to return
  • VALUES = DAX Function
  • US Superstore data = Name of the Dataset
  • Total Sales = Name of the Measure
When To Use TOPN In Power BI

2. After that, you see the names of the top 10 cities based on sales in the table.

TOPN function in Power BI

This way, you can see the top 10 cities based on sales value in Power BI.

Example – 3 (Top 1 Category Based on Profit Value)

Now, we see the top 1 category based on profit value. To do this, follow the below steps:

1. Click on the New table. Then, in the formula bar, put the below expression.

Top 1 Category = 
TOPN (            
    1,  
    VALUES('US Superstore data'[Category]), 
    CALCULATE(SUM('US Superstore data'[Profit])
))

Where:

  • Top 1 Category = Name of the new Table
  • 1 = The number of rows we want to return
  • VALUES = DAX Function
  • CALCULATE = DAX Function
  • SUM = DAX Function
  • US Superstore data = Name of the Dataset
  • Total Sales = Name of the Measure
Display Top N items and Others in Power BI

2. After that, you see the top 1 category in the table based on Profit.

How to Create Power BI TOP N()

This way, you can see the top 1 category in the table based on Profit value in Power BI.

How to Use the Top N Function in Power BI Measure

Now we see how to use TOPN () In Power BI measure.

1. Under the Home tab, click New measure.

power bi measure top n

2. Then, in the formula bar, put the below expression.

Best Selling Day = 
CONCATENATEX (
    TOPN (
       10,
        'US Superstore data',
        [Total Sales]
    ),
    FORMAT ( 'US Superstore data'[Order Date].[Day],'US Superstore data'[Order Date] )
)


In the above expression, the TOPN function generates a table; we need to use an aggregator function like CONCATENATEX to concatenate the table values. This measure will provide us with the best-selling date for each month.

Where,

  • Best Selling Day = Name of the new Measure
  • CONCATENATEX = DAX Function
  • 10 = The number of rows we want to return
  • US Superstore data = Name of the Dataset
  • Total Sales = Name of the Measure
  • FORMAT = DAX Function
  • Order Date = Name of the Column
power bi top n dax measure

3. Under the Home tab, expand Visual gallery(black box) -> Click the Table visual.

power bi top n filter measure

4. Then, using the +Add data option, add Order Date and Best Selling Day into the Columns field. Check the screenshot below.

power bi top n measure

5. After that, you see Best Selling Day in the Power BI table visual.

top n measure power bi

This way, you can use the top n function in Power BI measure.

Conclusion

I hope you follow the above steps to use the top n function in Power BI.

This tutorial taught us what the Power BI top n function is and how to use the top n function in Power BI. Additionally, we learned how to use the top n function in Power BI measure.

Also, you may like some more Power BI tutorials: