Select Distinct Logo Clear Background

Business Analytics Blog

Advanced Top N Filter Power BI

Advanced Top N filters in Power BI

The basic Top N filter in Power BI can be very useful. But if you need to show the values of the whole you will need to use an advanced Top N Filter Measure

This guide shows you how you can set it up

Advanced Top N filter in Power BI

If you use the standard top N filter you would see something like this

Standard Top 5 filter in Power BI

We outlined how this works in this post
TOPN in Power BI – Select Distinct

The problem with this method is that it ONLY shows the values for the top 5, but quite often you need to show the whole value with an aggregate row showing the ‘other’ values

We want the users to be able to use a slicer to select how many of the Top N values to see, but to also show the balance as ‘Others’

Overview of the steps involved

Detailed steps

1.Ensure there is a measure value for the ranking

You may have a measure already, but if not create one

Create a New Measure in Power BI

For illustration we are creating a measure called ‘Gross Sales New’ as the sum of the Gross Sales field

New measure starting point

DAX Code

Gross Sales New = SUM(Financials[Gross Sales])

paste this code over where it says Measure =, this creates the new measure

New Measure DAX code

2. Create a new table to determine the values for other

New table in Power BI

Use this DAX code to create the new table

TopN Names =
(ALLNOBLANKROW( Financials[Country]),

This creates a new table in the Fields Pane

New table in the fields pane

This table contains all the values in the matrix, plus ‘Other’ as the last row

3. Now Create a parameter to set the number of top rows to show

New parameter icon in Power BI

In the new parameter dialog enter as follows:

Name                    >> TopN
Data Type            >> Whole Number
Min                        >> 0
Max                       >> 20     (Optional, TopN results required)
Increment           >> 1       (Usually, may be different)
Default                 >> *       (Optional)
Add Slicer            >> Tick  (Optional, will show slicer on report)

New Parameter settings in Power BI

This creates a new Parameter table

New Parameter table

And slicer for the users to select a value for the Top N

Slicer for the Top N selection in Power BI

4. Add a new measure to the Financials Table

This applies a ranking to all values in the rows, including others, based on the measure.

Right click Financials, select New measure, then replace contents of code box with

Ranking =
VAR CatToRank = [TopN Value]
VAR Ranking =
ALLSELECTED(‘TopN Names'[Country]),
[Gross Sales New]
VAR IsOtherSelected =
SELECTEDVALUE(‘TopN Names'[Country]) = “Others”
VAR Result =
IF (
CatToRank + 1,
IF ( Ranking <= CatToRank , Ranking, ranking + 1)

DAX code for the measure

This creates a new measure called Ranking in the Financials table

Ranking measure in Power BI

5. Make the rows visible if their rank is within the Top N results

Create another measure with this code

RowVisible =
VAR Ranking = [Ranking]
VAR TopNValue = ‘TopN'[TopN Value]
VAR Result = INT( Ranking <= TopNValue + 1)

Compute a value for ‘Others’

Add a new measure to the Financials table.

Paste the following into the code box.

Gross Sales New Amount =

VAR NewMeasureAll =

CALCULATE( [Gross Sales New], REMOVEFILTERS(‘TopN Names'[Country]) )


IF (

ISINSCOPE(‘TopN Names'[Country] ),

VAR TopNToRank = [TopN Value]

VAR IsOtherSelected = SELECTEDVALUE(‘TopN Names'[Country]) = “Others”

VAR TopNWithMeasure =


ALLSELECTED(‘TopN Names'[Country]),

“@amt” , [Gross Sales New]


VAR TopNCat = TOPN( TopNToRank, TopNWithMeasure, [@amt] )

VAR TotalTopN = SUMX( TopNCat,[@amt] )

VAR Result = IF ( IsOtherSelected, NewMeasureAll – TotalTopN , [Gross Sales New])

RETURN Result ,



This creates a new measure which is used in place of the original. This is necessary as the
total of “Other” results has to be recalculated when the TopN value changes.

Filter and Sort Results

Enable “Other” values by Joining TopN Names table to the Financials table.

Open the Model window using the bottom icon in the left menu

Modelling icon in Power BI

Create a connection (Join) between the two Country objects

set relationship between the tables

Drag the Country item in Financials to the Country item in TopN Names

This creates a join between the two tables wherever the Country Field is used.

Check the properties of the join. Right click on the arrow in the join connector, select Properties

join properties in Power BI

Ensure the following settings are correct:

Cardinality                                          >> Many to One
Cross Filter Direction                      >> Single
Make this Relationship Active     >> Ticked

Filter to show only TopN and Other

As it stands the Matrix is showing the original values, and all values. The Row Visible measure can be used to filter as only TopN and Other

Ensure the Matrix is selected.

Drag Row Visible item from Fields Pane to the Add Data field in the Filter Pane

Change the Show Items drop down to ‘IS’
Add ‘1’ to the value box
Click Apply Filter

filter settings in Power BI

The values in the Matrix should change to show only the TopN values. (If a Row item is not in the TopN, the Row Visible value will be 0. Remember, the Other item is not yet showing.)

Include the new values in the Matrix

Swap Gross Sales New measure for the Gross Sales New Amount in the Visualisations Pane, Values box

This changes the values in the Matrix. Note ‘Other’ is now shown.

Matrix in Power Bi showing Other

By default this is sorting the values by country ascending, we want the values to sort descending, but with the ‘others’ to always be last

Add Ranking to the Values of the Matrix

We add the ‘Ranking’ measure into the matrix

Order the Ranking values Ascending. Click on the column header in the matrix until the Arrow points up and the ranking is from 1 upwards.

matrix with other values sorted correctly

You now have an Advanced Top N Filter in Power BI

This technique is applicable to other visualisations.

For example, duplicate the Matrix and change type to Pie Chart. This example clearly shows the top 5 as representing around a quarter of the whole, with ‘Others’ showing the majority

In this case we do not need Ranking, it can be removed

Pie chart showing the Top N plus others in Power BI


Learning how to create this Advanced Top N Filter in Power BI is a little complex, but worth persevering with.

Having the option to select the Top N value using a slicer in Power BI is a great way to allow the user to interact with the data

Subscribe to our channel to see more tips and timesavers

Select Distinct YouTube Channel

Or find other useful SQL, Power BI or other business analytics timesavers in our Blog

Our Business Analytics Timesavers are selected from our day to day analytics consultancy work.  They are the everyday things we see that really help analysts, SQL developers, BI Developers and many more people.

Our blog has something for everyone, from tips for improving your SQL skills to posts about BI tools and techniques. We hope that you find these helpful!

Business Analytics Blog

By Simon Harrison

Simon Harrison Founder of Select Distinct Limited and a business intelligence expert