What is the Power BI TOPN function?
The Power BI TOPN function is a function that returns the top N rows of a specified table based on evaluated expressions. It is a powerful tool in Data Analysis Expressions (DAX) in Power BI for performing data analysis and creating data visualization. Power BI TOPN function has multiple benefits that include:
- Easy identification of the top or bottom values in your dataset.
- Flexibility to display your dataset with additional provision of Power BI data filters and sorting.
- Performing complex calculations using DAX functions.
Table of contents
Key Takeaways
- Power BI TOPN function returns the top N rows of a specified table based on evaluated expressions.
- Power BI TOPN allows you to specify the number of records, sort order value, and sort order for your dataset.
- You can use Power BI TOPN DAX, Power BI Top N filter, and Power BI Top N measure for your data visualization requirements.
- Consider the limitations of the Power BI Top N function before you recommend using it in your visualization.
- Power BI Top N can be combined with other Power BI features such as drill-through or cross-filtering options.
Syntax
The syntax for Power BI TOPN is outlined below:
TOPN(<N_Value>, <Table>, <OrderBy_Expression>, [<Order>[, <OrderBy_Expression>, [<Order>]]…])
It comprises four parameters that include:
| Parameter Name | Mandatory/Optional | Description |
|---|---|---|
| N_Value | Mandatory | It specifies the number of rows to be returned. It can be any DAX expression returning a scalar value, where the expression is to be evaluated multiple times (for each row/context). |
| Table | Mandatory | It can be any DAX expression that returns a table of data from where to extract the top ‘n’ rows. |
| OrderBy_Expression | Optional | You can specify any DAX expression where the result value is used to sort the table and evaluate for each row of the table. |
| Order | Optional | This parameter specifies how the ordering of the results will be performed. It can have the following values:0 (zero) or FALSE. Sorts in descending order of values of Order. Default when Order parameter is omitted.1 or TRUE. Ranks in ascending order of OrderBy. |
For example, the measure below provides the top 50 products sold by sales amount.
= SUMX(
In the next section, we will go through how to use the Power BI TOPN function.
How to use the Power BI TOPN function?
To use the TOPN function in Power BI, follow these steps:
Step 1: Launch Power BI Desktop in your system. Import the dataset into Power BI using the Get data option.
Step 2: Navigate to the Visualizations pane, choose a visual, and map the data fields to the visual.
Step3:
- Navigate to the Filters pane.
- Expand the data field filter card.
- Choose the Filter type as Top N and specify the number of rows along the By value choice.
- Click on the Apply filter option to save the changes. It will filter only the specified number of rows in the result set.
If you are using Power BI TOPN DAX, follow the instructions below.
- Navigate to the Modeling tab and choose the New table option. Enter the formula using the TOPN function in the formula bar.
- TOPN(5, eCommerceSales, SUM(Sales[Amount]))
- Specify the input parameters with appropriate data fields from the Data pane.
- Optionally, provide the order by and sorting parameters for the data results.
- Press Enter to create the new calculated table in Power BI. Use this table for your reporting purposes.
Examples
In this section, we will go through a few examples demonstrating the Power BI TOPN function.
Example #1 – Power BI TOPN filter
In this example, we will demonstrate Power BI TOPN filter functionality using the World Population Statistics – 2023 dataset. The World Population Statistics – 2023 dataset 2023 World Population Data Sheet published by the US Census Bureau.
To use the Power BI TOPN filter, follow the instructions below:
Step 1: Import the dataset into Power BI using the Get data option and click on the Load button to load it into Power BI.
Step 2: Navigate to the Visualizations pane. Choose the Table visual icon and map the data fields from the Data pane to the Columns section.
Step 3: Navigate to the Filters pane and expand the country filter card.
Step 4: Click on Filter type and choose TopN option from the dropdown menu.
Step 5: Specify the parameters, i.e., Top and number of rows to display. Here, we have specified the rows as 20. Additionally, provide the order by value. For example, here, we have specified the ordering based on the 2023 population.
Step 6: Click on the Apply filter to save the changes.
Now, you can see only the top 20 records based on the 2023 populations displayed in the report canvas.
Step 7: Repeat steps 2-4 to create another table visual in the report canvas.
Step 8: Specify the parameters, i.e., Bottom and number of rows to display. Here, we have specified the rows as 20. Additionally, provide the order by value. For example, here, we have specified the ordering based on the 2023 population.
Click on the Apply filter to save the changes. Now you can see the bottom 20 records based on the 2023 populations are displayed in the report canvas.
Step 9: Similarly, you can repeat the above steps to create additional reports in the report canvas. Here, we have created the Top 20 countries by land area using the TopN filter.
Step 10: Apply formatting to add Title and display options to the visuals. Now your report looks as below.
Example #2 – Power BI TOPN DAX
In this example, we will use Power BI TOPN DAX using the US consumer purchase trends dataset. This dataset contains features related to customer shopping preferences including customer age, gender, purchase amount, preferred payment methods, frequency of purchases, and feedback ratings.
To use the Power BI TOPN DAX, follow the instructions below:
Step 1: Import the US consumer purchase trends dataset into your Power BI Desktop using the Get data option and load it into Power BI.
Step 2: Navigate to the Modelling pane and click on the New table option.
Step 3: In the formula bar, write the formula specifying the TOPN function to create a calculated table containing records of products based on Top 50 sales. Click on the Commit icon to save the changes.
Most Products Purchased based on Top 50 Sales = TOPN(50,US_Consumer_Purchase_Trends,US_Consumer_Purchase_Trends[Purchase Amount (USD)],DESC)
It will create the calculated table Most Products Purchased based on Top 50 Sales.
Step 4: Navigate to the Table view in the left-hand navigation of the Power BI window.
You will see the filtered dataset containing records based on the top 50 sales.
Notice that the calculated table contains 88 records as opposed to the specified 50 rows in the TOPN function. It is due to the ties in the dataset, i.e., purchase amount with multiple similar values.
Example #3 – Power BI TOPN Slicer
In this example, we will demonstrate the use of Power BI TOPN slicer using the eCommerce Order Details dataset. The eCommerce Order Details dataset contains the order ID, with the order price, quantity, profit, category, and subcategory of product.
To use the Power BI TOPN slicer, follow the instructions below:
Step 1: Import the eCommerce Order Details dataset into your Power BI Desktop using the Get data option and load it into Power BI.
Step 2: Navigate to the Visualizations pane. Select the Table visual icon and map the Columns section with the data fields in the Data pane.
Step 3: Navigate to the Filters pane and expand the Sub-category filter card.
- Click on Filter type and choose the TopN option from the dropdown menu.
- Specify the Show items, i.e., Top and number of rows to display.
- Here, we have specified the rows as 5.
- Specify the By value for the order by expression for your dataset. Here we have specified the order by expression based on the profits.
Click on the Apply filter to save the changes.
Step 4: Similarly, create a Table visual to display the Top 20 orders by sales amount by choosing the Table visual from the Visualizations pane and mapping the data fields.
Step 5: Navigate to the Filters pane, expand the Order ID filter card, and choose Filter type as Top N.
Specify the Show items and By value, as shown below.
Click on the Apply filter once the changes are made.
Step 6: Navigate to the Visualizations pane and choose the Slicer visual.
Here, we have added 2 slicers i.e. Category and Sub-category to the report canvas.
Step 7: Apply the formatting to the visuals such as adding the Title, font, and other customizations.
Step 8: Once the changes are applied, you will see the final visual in the report canvas, as shown below.
Important Things to Note
- If you provide N_value as 0 or negative, the Power BI TOPN function will result in an empty table and the rows will not be sorted in any particular order.
- Power BI TOPN doesn’t always return N number of rows. It can return more than N rows if there are ties at the N-th row of the results. In the event of a tie, Power BI TOPN returns all the tied rows.
- While TOPN supports the order of the results, it does not guarantee any sort order for the results.
- DirectQuery mode doesn’t support the Power BI TOPN function if you are using it for calculated columns or row-level security (RLS) rules.
Frequently Asked Questions (FAQs)
Yes, you can customize the number of items displayed in the TopN filter. When you define the TOPN function, specify the N_value with the number of rows you want to return. Additionally, you can specify the OrderBy_Expression to define the sort value of the results and sort order i.e. ascending or descending.
Yes, you can combine TopN filtering with other Power BI features like drill-through and cross-filtering.
• For the Drill-through feature, ensure that your dataset supports the hierarchy structure. You can use drill-through functionality on the filtered dataset to get a view of the underlying granular details.
• You can use the cross-filtering feature on top of the TOPN results. When you apply cross-filtering, it will interact with other visuals in a report and apply a contextual filter showing the data applicable to the selected visual.
Yes, you can apply TopN filtering across multiple visuals in a report. You can apply the filter context either to a report level or page level. You can also consider using cross-filtering across multiple visuals in a report.
Both TopN filtering and sorting features help you in organizing your data. However, each of these features serves different purposes as outlined below:
• TopN filtering helps you retain the specified number of rows based on the evaluated expressions while rejecting the rest of the dataset.
• Sorting data enables you to organize the rows of the evaluated expressions in a specific order i.e. either ascending or descending order, however, it displays all the records and doesn’t filter out any dataset.
Recommended Articles
This has been a guide to Power BI TOPN. Here we explain how to use TOPN in power bi, syntax, parameter, examples, and points to remember. You may learn more from the following articles –
Leave a Reply