TOPN

How to Use the Power BI DAX function TOPN

Understanding the TOPN Function

The TOPN function in Power BI is used to retrieve the top N items from a table based on a specified column and measure. It can be used to find the top N products, customers, or any other item in a table. The syntax of the TOPN function is as follows:

TOPN ( ❰N❱, ❰Table❱, ❰Expression❱, [❰ColumnName❱] )

- `❰N❱`: This is the number of items you want to retrieve.

- `❰Table❱`: This is the table from which you want to retrieve the items.

- `❰Expression❱`: This is the measure or calculation you want to use to determine the top N items.

- `[❰ColumnName❱]`: This is an optional argument that specifies the column you want to use to break ties. For example, if two items have the same value for the measure, the column specified in this argument will be used to determine which item should be included in the top N.

Examples of Using TOPN Function

Let's take a look at some examples of using the TOPN function in Power BI.

Example 1: Retrieve Top 5 Products by Sales

Suppose you have a sales table that contains information about the products sold and the amount of sales for each product. You want to retrieve the top 5 products based on their sales. To do this, you can use the TOPN function as follows:

TOPN ( 5, ‘Sales’, [Total Sales] )

In this example, `5` is the number of products you want to retrieve, `'Sales'` is the name of the table, and `[Total Sales]` is the measure you want to use to determine the top 5 products.

Example 2: Retrieve Top 3 Customers by Order Count

Suppose you have a customer table that contains information about the orders placed by each customer. You want to retrieve the top 3 customers based on the number of orders they have placed. To do this, you can use the TOPN function as follows:

TOPN ( 3, ‘Customers’, COUNTROWS(‘Orders’), [‘Customer Name’] )

In this example, `3` is the number of customers you want to retrieve, `'Customers'` is the name of the table, `COUNTROWS('Orders')` is the measure you want to use to determine the top 3 customers, and `['Customer Name']` is the column you want to use to break ties.

The TOPN function is a powerful DAX function that can be used to retrieve the top N items in a table based on a specified column and measure. In this article, we explored the syntax of the TOPN function and provided some examples of how it can be used in Power BI. We hope this article has helped you understand how to use the TOPN function in your Power BI reports.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)