How to add a top or bottom n records filter in Microsoft Power BI

Estimated read time 5 min read


You don’t need functions to return the top or bottom records in Microsoft Power BI. A simple filter is all that’s required.

Microsoft Power BI logo on phone screen stock image.
Image: sdx15/Adobe Stock

Returning the top or bottom records from a dataset usually requires a function or expression. Microsoft Power BI uses a filter that end users can then select to modify the visualization accordingly. The good news is that the filter is easy to implement. The bad news is that it’s static. That means, the user only has one top or bottom choice when applying the filter.

In this tutorial, I’ll show you how to use Power BI’s built-in filter to display a set number of top or bottom values in a Power BI visualization.

I’m using Power BI on a Windows 10 64-bit system. You can download the demonstration .pbix file, AdventureWorks Sales from Github. Specifically, we will work with the Customer and Sales tables. Once downloaded, double-click the .pbix file to open it in Power BI and follow along or use a .pbix file of your own.

SEE: Microsoft Power Platform: What you need to know about it (free PDF) (TechRepublic)

About filters in Power BI

Power BI offers three types of filters:

  • Power BI adds automatic filters when building a visualization. Users can modify the filters in specific ways, but they can’t be removed.
  • Manual filters are available in the Filter pane. Users can drag them into buckets as needed.
  • Advanced filters are available but are more specialized.

In this tutorial, we’ll add a manual filter via the Filters pane in the Report window. We’ll use Power BI’s Top filter, which uses the TOPN DAX tabular function to select the top or bottom values from an input table based on an expression. An input table in Power BI is another name for the source table. TOPN DAX returns a subset of the input table, but only the top or bottom values. This function uses the following syntax:

TOPN(<n_value>, <table>, <orderBy_expression>, [<order>[, <orderBy_expression>, [<order>]]…])

Explanations for the arguments follow:

  • n_value is the count of items you want to return from the input table (data source table).
  • table is the input table.
  • orderBy_expression is the expression used to sort in order to return the top or bottom records.
  • order specifies whether the results should come from top or bottom of the table. The default is DESC which means TOP and ASC will return records from the bottom.

Before we can add a filter, we need a visualization.

How to build a matrix visualization in Power BI

For demonstration purposes, we’ll create a simple matrix visualization (Figure A) for two reasons: They’re the easiest visualization to apply, and in this case, a matrix is readable and easy to discern.

Figure A

Power BI matrix visualization option circled in the visualizations menu
Figure A: A simple matrix visualization based on two tables, Customer and Sales, in AdventureWorks.

To create this visualization, do the following:

  1. In the Report window, Click Matrix in the Visualizations pane.
  2. Expand the Customer table in the Fields pane, and check Customer.
  3. Expand the Sales table in the Fields pane, and check Sales Amount and Order Quantity.

The result is a matrix visual that sums the number of orders and calculates the total sales for those orders by customer.

Now that we have a visualization, let’s apply a Top filter in Power BI.

How to add a Top filter to a visualization in Power BI

Power BI’s Filters pane displays to the right of the visualizations in the Report window. If necessary, click the > icon in the upper-right corner to expand it. Using this pane, you can set filters at three different levels:

  • The visual level
  • The page level
  • The report level

The filtering pane lets you choose between basic and advanced filtering (Figure B). Click the ^ icon to the right of the Customers bucket. Doing so opens a list of basic filters. The word All denotes an unfiltered field.

Figure B

Basic filtering filled out in the Filter type search field in the Power BI Filters menu
Figure B: Display the basic filters for the Customers field.

In other words, the visualization displays all the values in that field. If you select one of the items in the filter, which in this case is a customer, the filter will reflect that by displaying that customer’s name instead of All.

Let’s add a Top filter that displays the top 10 customers by sales amount. To do so is a quick task.

If necessary, expand the Filters pane. The fields we want to filter by are already in the visualization, so we don’t need to add fields. From the Filter Type dropdown, choose Top N (Figure C).

Figure C

Filter options in Power BI
Figure C: Choose the Top filter.

Power BI displays Top in Show Items. Enter 10 in the bucket to the right. To display the bottom records, choose Bottom from this dropdown. Then, drag the Sales Amount field from the Fields pane to the By Value bucket (Figure D). To see the filter at work, click Apply Filter under the Value By bucket.

Figure D

The Filters options in Power BI with the By value drop-down open
Figure D: Add the Values By field, Sales Amount.

As you can see in Figure E, the visualization now displays only 10 records, and the values in the Sales Amount field are the top 10 sales amounts. The filter sorts them by customer and not the sales amount value. To temporarily sort by the sales amount column, click its header cell.

Figure E

A data table in Power BI displaying information for Customer and Sales Amount
Figure E: Apply the filter to see the top ten customers by Sales Amount.

Stay tuned

As mentioned, the end consumer can’t choose the number of records to see because you set that number when you add the filter. In a future article, we’ll add a slicer that lets you choose the number of returned top or bottom records.



Source link

You May Also Like

More From Author