Do you need to show the top 10 items in your Power BI report?

A common requirement is to display the top 10 records based on specific criteria. This step-by-step article will explore different methods to achieve this in Power BI.

This article will cover how to display the top 10 records using the Top N filter, utilizing the RANKX function, leveraging the Advanced Filter feature, employing the TOPN DAX function, and using Power Query.

By the end of this article, you will have a comprehensive understanding of various approaches to showcase the top 10 records in Power BI.

The Dataset

The dataset you are going to use for this demo is fictitious superstore sales data for around 10K rows. The dataset consists of five columns, namely Product ID, Category, Sub-Category, Product Name, and Sales as columns.

Display Top 10 Records Using the Top N Filter

In this section, I will focus on using the TOP N filter in Power BI to show the top 10 records.

The TOP N filter allows you to filter your data dynamically and display the highest values or records that meet specific criteria. By following the step-by-step instructions below, you will learn how to utilize the TOP N filter in Power BI.

1. Let’s bring your Superstore_Sales.xlsx data into Power BI by navigating to Get data (placed under the Data section in the Home tab).
2. Click on the Excel workbook option to be able to load data from it.
3. Navigate to the file location and load the required table in PBI.

There are two other options to select the data from the Excel workbook –

• As soon as you open the Power BI Desktop app, you will see an option named Import data from Excel on the empty canvas.
•  Or you can see the Excel workbook option directly placed under the Data section of the Home tab beside the Get data dropdown.

Once the data is in there, you must create a visual representing or showing the top 10 records. The Table visual is an excellent option for this.

Go to the Visualizations pane on the right-hand side of the Power BI Desktop screen and select the type of visual you want to use, such as a bar chart, column chart, or table. This example will use the Table visual for this demonstration.

Once the visual is selected, on the left side, you will see a table visual card occupying some space from the empty canvas. You will, though, need to add Columns to it in order to see a meaningful result and eventually be able to apply the Top N filter.

I will go with the Sub-Category column together with Sales. It will summarize the table in a way that for all the seventeen subcategories, total sales will be returned.

📝 Note: Suppose you want to summarize the numeric Sales column based on any other aggregator such as Average. In that case, you go to the Columns section again and click on the dropdown from the right side of Sum of Sales, and you will see a lot of options to summarize the numeric column. The above screenshot should explain this.

Once you set up the visual in this way, navigate to the right side of the canvas again and click on the arrow next to the Filters pane to expand the filtering options for this visual as well as for the entire report page.

Inside the Filters in this visual section, you will see Sub-Category and Sum of Sales, both of our filter cards associated with the respective columns sitting comfortably.

1. Click on the Sub-Category filter card to expand it.
2. Select Top N from the Filter type dropdown.
3. Once enabled, you can specify the number of records you want to display through this feature. Since you are more interested in showing the top 10 records, let’s set the number as ten inside the Show items section.
4. To show a categorical column in the Top N manner, there needs to be some numeric measure against which the Top N is decided. In this case, I am expecting the top 10 records to be shown based on the Sum of Sales.
5. Therefore, drag the Sales column from the Data pane to the By value section in Filter on this visual section under the Filters pane. It will automatically pick Sum as the method to aggregate the data.
6. Finally, click the Apply filter button at the bottom right to apply this filter to the visual you created.

If you go back to your visual again, you will see only 10 categories are now displayed rather than 17 as of now. This means that the Top N filter you applied is working as intended.

Wait a minute! 🤔

The Top N worked fine, but the order in which these subcategories are shown differs from what I expected. The subcategory with the highest total sales should appear on the first row, followed by the second-highest, third-highest, and so on.

This means you have to fix the sorting order. Just go to the Sum of Sales header in your visual and click on it once. It should sort the data in descending order for you. If it doesn’t, click there again, and you will be through.

This is just a shortcut to sort the data based on any particular column irrespective of what datatype it belongs to.

Whola! You have successfully shown the top 10 rows from your dataset in the visual.

Display Top 10 Records Using the Advanced Filter

Now that you know how to display the Top 10 records in Power BI using the built-in Top N functionality, it is time to see any alternatives to complete this task. Ideally, there are two or three different ways of getting one task done in Power BI, and for displaying Top 10 records, there are a few, too.

In this section, you will focus on using the Advanced Filter option in Power BI to show the top 10 records. The Advanced Filter allows you to filter your data dynamically and display the highest values or records that meet specific criteria. By following the step-by-step instructions below, you will learn how to utilize the Advanced Filter in Power BI to show the top 10 records.

The first step of building any interactive visual is to bring the data to the Power BI Interface. Since you are already well aware of it, if you walked through the first section of this article, I will not spend time on this step. Let’s assume you have the data in Power BI.

Let’s build a Clustered bar chart this time.

1. Through the Visualizations pane, click on the Clustered bar chart visual.
2. To set it up, drag the Sub-Category to the Y-axis and Sales to the X-axis (which will then be aggregated).

📝 Note: Since you are working with a Clustered bar chart, a field containing categorical values or the one you are going to use for ranking is on the Y-axis, and the numeric/fact field is on the X-axis. If you are preparing a Clustered column chart, the dynamics will be precisely reversed to the current one. The field containing categorical values will be placed on the X-axis, and the field with numeric values will be on the Y-axis.

Select the visual and navigate towards the Filters pane placed on the right-hand side of the canvas before the Visualizations pane. Expand the Filters pane.

Inside the Filters pane, you see Sub-Category and Sum of Sales as two filter cards available to add the filter on this visual. Click on the Sum of Sales filter card to expand.

📝 Note: This time, you are filtering out the numeric value filter card because that’s how you will be able to show the top 10 in this scenario.

You will see a dropdown for advanced filtering options. Choose the “is greater than” operator from that list, and inside the textbox below that, enter a value of 100000 (0.1M). Click the Apply filter button to implement this filter on the Clustered bar chart.

If you look at the chart now, it only shows ten subcategories and their respective sales numbers as a Clustered bar chart.

Explanation

Here, by looking at the chart, you already know that the top 10 Sub-Categories are until Appliances (which sales around 0.11M in total). So, to make sure anything below Appliances is filtered out, you need to provide a threshold value as a filtering criterion so that the rest of everything is filtered out below 0.11M total sales value.

Since we are using the “is greater than” operator, if we provide a value of 0.11M, it will also filter out the Appliances and show the top 9 only. Instead of that, I chose a value that is precisely smaller than the threshold for the Appliances and used it inside the filtering criteria.

📝 Note: If you don’t want to use a value lower than the threshold value as a filtering criterion, you need to select the “is greater than or equal to” operator to show the top 10 values. You can then use the exact threshold value (which is 0.11M in this case).

Display Top 10 Records Using the TOPN Function

Now is the time to move further towards DAX functions that can generate the top 10 records for you in Power BI. In this section, you will focus on using the TOPN function in Power BI to show the top 10 records from a table based on the expression.

The TOPN function allows you to filter and rank your data dynamically, providing valuable insights into your dataset. By following the step-by-step instructions and examples below, you will understand how to utilize the TOPN function in Power BI.

More interestingly, TOPN is a tabular function that best provides the subset of the original dataset based on the top number of values you ask for (in this case, you are asking for the top 10 values).

Therefore, for this section, you will work more closely with the Table view rather than creating visuals representing the top 10.

The syntax for the TOPN function is as shown below:

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

• N_Value – represents the number of records you want to return from the original table.
• Table – A table name or any DAX expression that returns a table from which the top n records need to be extracted.
• OrderBy_Expression – is an optional argument representing the DAX expression whose return value is used to evaluate each row of the table and sort it accordingly.
• Order – an optional argument that decides how to sort the output of OrderBy_Expression values. 0 (zero)/FALSE sorts the data in descending order, and 1/TRUE sorts the data in ascending order.

In your Power BI Desktop app, move towards the Table view, which is placed below the Report view on the left side pane. You will see the SuperStore_Sales table.

Click on the New Table option placed in the Calculations group under the Table Tools tab. This will allow you to create a new table based on a DAX expression.

The most straightforward approach is to create a subset of the Top 10 records from the current table. For that, use the following DAX Code inside the Formula bar that appears as soon as you click on the New table option.

Top10_subset = 
TOPN(
    10, 
    SuperStore_Sales, 
    SuperStore_Sales[Sales],
    FALSE
)

Here, you ask the function to return the top 10 rows from the SuperStore_Sales table based on their Sales. And that’s precisely what the screenshot above suggests.

But wait a minute! Why are you seeing all the columns in the output? Well, that’s because you haven’t specified against which column the top 10 sales should be shown. You can’t do it directly either inside the TOPN function. You will have to summarize the data. Let’s see how.

To show the top 10 Sub-Categories based on the Sales value, let’s use the SUMMARIZE function from Power BI in combination with the TOPN. The function code is as shown below.

Top10 = 
TOPN(
    10, 
    SUMMARIZE(
        SuperStore_Sales,
        SuperStore_Sales[Sub-Category],
        "Sales",
        SUM(SuperStore_Sales[Sales])
    ),
    [Sales],
    FALSE
)

Explanation

The SUMMARIZE function first navigates to the SuperStore_Sales table, Groups the entire data by Sub-Category (So there will be 17 rows each representing a single category), and labels it “Sales.”

Then, the Expression based on which the data should be grouped is used. In this case, we want to return the total Sales amount per Sub-Category. Therefore, the SUM function is applied to the Sales column.

Then, this entire summarization is used as a table input inside the TOPN function, and a dummy “Sales” label created in summarization is used as the OrderBy_Expression. Finally, the TOPN does what it does best. Returns the 10 rows (because you specifically asked for 10 rows to be returned).

When you try to understand the context of the SUMMARIZE function here in this code, you will realize that it creates a virtual table of total sales amounts grouped by subcategories and then used as an argument inside the TOPN function.

Therefore, you have another way of displaying the top 10 rows in Power BI. But this time on the Table view rather than a Reporting view.

Display Top 10 Records Using the RANKX Function

Until now, you have three different ways of displaying the top 10 records in Power BI. Let’s take our game further and introduce a fourth way of doing so. In this article, you will focus on using the RANKX function in Power BI to show the top 10 records. The RANKX function allows you to rank records based on a measure and then filter them to display the top values. By following the step-by-step instructions below, you will learn how to utilize the RANKX function in Power BI to show the top 10 records.

You will also see another approach altogether where you will create a new measure itself, which filters the data on its own, and you don’t need to apply a separate filter to show the top 10 values based on the ranks.

The syntax for the RANKX function is as shown below –

RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])

Where,

• table – name of the table or any DAX expression that returns a table will do
• expression – specifies the DAX expression performed on each table row and returns a single value for each category.
• value – a DAX expression that generates a single value based on which the ranking should happen. It’s an optional argument.
• order – specifies how the value generated in the previous argument should be ranked. Either high to low or low to high. It is an optional argument. If set to 0 (zero)/FALSE, the ranking will happen in descending order (also a default order). Otherwise, it will be in ascending order when the value is set to 1/TRUE for this parameter.
• ties – an optional argument that specifies how the ranking should be determined in case there is a tie in values. If set to Skip, the ties are skipped, and ranking will be incremental, irrespective of the ties. If set to Dense, all values with ties will receive the same rank.

Let’s create a simple example where we create the ranking based on the Sum of Sales for all Sub-Categories in the SuperStore_Sales table in Power BI.

In the Power BI Report view, go to the Home tab and click on the New measure option.

Write down the following piece of DAX to create a measure named “Rank by Total Sales.”

Rank by Total Sales = 
RANKX(
    ALL(SuperStore_Sales[Sub-Category]),
    CALCULATE(SUM(SuperStore_Sales[Sales]))
)

Here, the RANKX function CALCULATEs the SUM of the Sales amount from the table, and based on that, it ranks ALL of the Sub-Categories.

If you add this measure against the Sub-Categories in a table visual, you will see the rank for each of them, as shown above.

📝 Note: The Table visual is sorted based on the values for Rank by Total Sales column for better realization.

Now, click on visual > go to the Filters pane > expand the Rank by Total Sales filter card > Select “is greater than or equal to” operator > use value 10 in the text box below > click on Apply filter button.

This will filter the visual based on the first 10 ranks assigned to sub categories.

Now, if you add a Sales column to this visual, you will get a perfect top 10 visual of subcategories based on the total sales amount.

This is one way of utilizing the RANKX function, and it’s capabilities together with the visual level filter in Power BI to return the top 10 values.

However, you can create a dynamic top 10 measure itself with the help of RANKX without needing the visual level filter. Let’s see how.

Create a new measure named Top10_RankX in the Power BI Reports pane with the help of the following DAX code.

Top10_RankX = 
VAR SubCategotryRank = 
RANKX(ALL(SuperStore_Sales[Sub-Category]), CALCULATE(SUM(SuperStore_Sales[Sales])), , DESC)
RETURN
IF(SubCategotryRank <= 10, CALCULATE(SUM(SuperStore_Sales[Sales])), BLANK())

The first part of the code remains the same as the previous example. The only thing is we are ordering these ranks in descending order for generalization and then assigning these all rankings to a temporary variable called SubCategoryRank.

Now, for this rank, we are asking the system to RETURN the SUM of the Sales amount if the rank for subcategories is less than or equal to 10. If the condition doesn’t satisfy, the system will return blank.

If you see the Power BI visual now, it returns values for only those subcategories along with the Top10_RankX for which the rank is between 1 to 10. for the rest subcategories, there is no sales value because we mentioned returning BLANK() for any subcategory that ranks above 10.

This summarized the RANKX approach to display the top 10 records in Power BI. That’s really cool because now you have four ways to show the top 10 in Power BI, and it must already be feeling like being a powerful ninja into the field.

However, as promised, one last way of showing the top 10 records through the Power Query editor is coming your way.

Displaying Top 10 Records through Power Query

There is no such function in Power Query that can return the top 10 values directly. However, with some intelligent hacks, you can also achieve these feet. The only challenge is this will change the entire schema of your data. Therefore, I would instead create a copy of the SuperStore_Sales table query in Power Query Editor.

Navigate to the Power Query Editor (You can launch it by clicking on the Transform Data option inside the Home tab) and create a copy of the original SuperStore_Sales table. Rename it as Top10_Data.

To show the top rows at first based on the Sales values, you need to sort the data based on the Sales column in descending order. Select the Sales column and click on the Sort descending button grouped under the Sort section inside the Home tab.

This will bring the rows with the highest sales value up and the lowest sales value to the bottom.

After this, click on the Keep Rows dropdown and select the Keep Top Rows option. As the name itself suggests, it would allow you to keep several of the top rows from your table.

The Keep Top Rows dialogue box that opens up next will ask you the Number of rows you want to keep in the layout. You just mention 10 there because you want to return the top 10 rows. Click OK, and that’s it!

You will see that the data is now reduced to 10 rows based on the descending Sales values.

The only difference here in comparison with the methods we used in the previous sections is the rows that are kept are based on the entire table layout available and not based on the total sales amount.

Conclusion

Let’s try to encapsulate the five different ways we explored to display the top 10 records in Power BI.

• The first approach is the easiest of them all. You already have a Top N filter functionality available for the categorical or ranking columns through the Filters pane in Power BI.
• You can also apply the Advance Filters on numerical columns, which act as aggregators and based on which the ranks are assigned while displaying the top 10 records. The Advanced Filters include greater than, less than, not equal to, etc operators to filter the data.
• The TOPN function is a tabular function and for it to give the top 10 records, you need to create a new table through the Model view tab. The function works pretty well solo or in combination with other aggregator functions such as SUMMARIZE, SUM, etc.
• The same is the case with RANKX. It in combination with Filters or functions such as ALL, CALCULATE, and conditional IF provides a top 10 overview of the records.
• Finally, you can also display the top 10 records through Power Query by sorting the data in descending order and then utilizing the Keep Top Rows functionality from the Keep Rows dropdown. However, make sure to create a duplicate of your table query because this will change the schema itself.

This was the last arrow in my quiver as long as it is about how to display the top 10 records in Power BI. But before we head out, are there any other interesting ways you came across to display the top 10 records in Power BI? Do let me know through the comment section. Until the next time, ciao!