Show Top N and Rest in Power BI

Technical Value

Hello Everybody, today I would like to show you how you can create a dynamic Top N selection in Power BI which shows you the rest of the data (the data that is not in the Top N). The October Update brought us a cool new Top N filter for Power BI. This is an awesome feature, but there is missing one important piece: The possibility to show the rest of data that is not included in the Top N.

Left: Topn N with rest - Right: Top N without Rest created by Power BI filter
Left: Topn N with rest - Right: Top N without Rest created by Power BI filter

Actually this is a though problem for Power BI because of the tabular model. The value "Others" does not exist in the column of a table (dimension). In Tabular Model you can only filter by values of columns, so if the value "Others" is missing, it is not possible to show it on axis or to slice data with it. The task of adding an additional member to the dimension should be done by the front-end, but at the moment Power BI is not capable to this.

So how to solve this ?

Let's start at the beginning, in the following screenshot you can see my data model. The data is an import from Adventure Works. A fact table with 4 dimensions. In this scenario we look at the DimProduct Table and at the column [ProductName] in detail.  

Power BI Datamodel with adventure works demo data
Power BI data model with adventure works demo data

There is only one way to bring in the missing member "Others" to the set, by creating a new table! For that I create a new table by using DAX. I do a simple union of our column [ProductName] with a new created row which has only one value

  1. "Others". ProductName =     UNION ( VALUES ( DimProduct[ProductName] ); ROW ( "ProductName"; "Others" ) )

To give the user a maximum of flexibility I create a parameter table which contains the values {3,5,10,20,50}. The user can use this table with the column [TopN] later on to filter the report. If you are interessted in how this works I recommend the dax pattern from sqlbi .

TopN Dynamic Filter
TopN Dynamic Filter

Now I have two new tables in my data model. Both aren't connected to any other table in the model. Since there is no physical relationship, I have to create a virtual relationship via dax by using a measure.  

Tables are not linked with the data model
Tables are not linked with the data model

The column I like to use for my top n analysis is  FactInternetSales[SalesAmt]. To keep things simple I create a measure. Sum of Sales Amount :=    SUM ( FactInternetSales[SalesAmt] ) Now I create 2 measures [Top X] and [Others]. Keep in mind that we have to use our new dummy column ProductName[ProductName], because this column contains the needed value "Others". [Top X] will calculate the Sales Amount for only the top n values from column ProductName[ProductName].  The tricky part is that there is no physical relationship, so we have to create a virtual one.

  1. Top X =
  2. VAR TopNumber =
  3.     IF ( HASONEVALUE ( 'TopN Filter'&SQUARE_BRACKETS_OPEN;TopN] ); VALUES ( 'TopN Filter'[TopN] ); 10 )
  4. VAR Rest =
  5.     COUNTROWS ( DimProduct ) - TopNumber
  7.     IF (
  8.         HASONEVALUE ( ProductName[ProductName] );
  9.         CALCULATE (
  10.             [Sum of Sales Amount];
  11.             FILTER (
  12.                 DimProduct;
  13.                 [ProductName] = VALUES ( ProductName[ProductName] )
  14.                     && CONTAINS (
  15.                         TOPN (
  16.                             TopNumber;
  17.                             ADDCOLUMNS (
  18.                                 ALL ( ProductName[ProductName] );
  19.                                 "Sales"; CALCULATE (
  20.                                     [Sum of Sales Amount];
  21.                                     FILTER ( DimProduct; DimProduct[ProductName] = EARLIER ( [ProductName] ) )
  22.                                 )
  23.                             );
  24.                             [Sales]; DESC
  25.                         );
  26.                         ProductName[ProductName]; VALUES ( ProductName[ProductName] )
  27.                     )
  28.             )
  29.         )
  30.     )

To explain the measure, the best way is to start with the innermoste function:

  • ADDCOLUMNS: Calculates the Sales Amount for all our rows in the dummy table (ProductName). It returns a table which contains the Sales Amount of all ProductName's from ProductName[ProductName]
  • TOP N: The TOPN filters the calcualted table from ADDCOLUMNS to the needed amount of top products.
  • FILTER and CONTAINS : We filter DimProduct so that only those rows remain that are in our TOPN Table. We do this by comparing the names of the products (both [ProductName] columns)
  • CALCUALTE: In the end DimProduct is filtered to the correct number rows (the top products). This filter context is handed into the [Sum of Sales Amount] calculation.
  • The HASONEVALUE are needed to prevent DAX from returning an error if more than one value ist returned for ProductName[ProductName] for example in the total of a calculation.

  The [Others] measure is a bit more simple. The trick here is  that we have to calcualte the number of rows that are not in the top n. For that I create the variable REST, which returns the number of rows from DimProduct which are not in the top n. We create a calcualted table (ADDCOLUMNS) which is filtered by the inverted top N (Rest). The "ASC" parameter in TOPN allows us to get the not top n rows from ProductName. After that we only have to sum this new calculated table. The sum is only shown when the values of

  1. Other =
  2. VAR TopNumber =
  3.     IF ( HASONEVALUE ( 'TopN Filter'&SQUARE_BRACKETS_OPEN;TopN] ); VALUES ( 'TopN Filter'[TopN] ); 10 )
  4. VAR Rest =
  5.     COUNTROWS ( DimProduct ) - TopNumber
  7.     IF (
  8.         HASONEVALUE ( ProductName[ProductName] );
  9.         IF (
  10.             VALUES ( ProductName[ProductName] ) = "Others";
  11.             SUMX (
  12.                 TOPN (
  13.                     Rest;
  14.                     ADDCOLUMNS (
  15.                         VALUES ( DimProduct[ProductName] );
  16.                         "Measure"; [Sum of Sales Amount]
  17.                     );
  18.                     [Measure]; ASC
  19.                 );
  20.                 [Measure]
  21.             )
  22.         )
  23.     )

  Now that everything is ready we can create a chart with our dummy column ProductName[ProductName] on the axis and our both measure as values. The measure is full interactive, so you can filter the table DimDate or the DimProduct itself. Everything is calculated dynamically on the fly. You can try it yourself by clicking on the next screenshot.  

TopN Analysis with Power
TopN Analysis with Power - Click here to see it live


Cross filtering is full supported
Cross filtering is fully supported


In this post I showed you how to create a dynamical top n/rest analysis. To be honest, I think it should be much easier to do that kind of analysis, but at the moment Power BI is missing this feature. The used DAX measure is a little bit complex, but still manageable. The big drawback of this solution is that I had to create a dummy table to get the members "Others" into the set. So you have to know which attributes your user likes to analyze by top n/rest approach. But it is always nice to have a pattern for recurring problems. I hope you find it helpful.


You can try it out right here:

Download the file.

This is a much discussed article and I’m very happy that this article helped some many of you. Some of you  have sent me mails or used the comment function in the blog to contact me. I’m very sorry, but for me it is not possible to answer detailed questions. It is always very time consuming and I often need more background information about your model, your data and your skill level.

Regards Lukas


Fr, 27.04.2018 - 14:17

Hallo Lukas Lötters,
thanks for your sharing. Howerver it is quite difficulte to follow with only text. COuld you please share me the Power BI file so i can have better understanding?
thank you in advance!

Mi, 02.05.2018 - 21:48

First, I did get this working with a version of AdventureWorks. I am very, very new to Power BI, so I could be wrong. But when I tested this I found the value returned for "Other" to not be correct. The Top n function appears to function but I have to do further testing on those results.

Do, 03.05.2018 - 21:16

I was able to puzzle out the issue with measure "Other". In the article you declared variable "Rest = COUNTROWS ( DimProduct ) - TopNumber". That is the problem. The correct solution is "Rest = DISTINCTCOUNT( 'DimProduct'[ProductName] ) - TopNumber.

Do, 11.03.2021 - 13:04

Thanks Steve this solved my issue. With Countrows the sum of others is the total of all instead of the total of Others. Many thanks.

Lukas Lötters
Di, 08.05.2018 - 09:47

Hi guys, thank you for all the feedback. I think there was a copy & paste error in the script. I updated the blog post. I also added the PBIX file for download and I embdedded the Report. I hope this work for you


Mo, 09.07.2018 - 15:28

Hi Lukas,
The link to Power BI file doesn't work anymore. Could you please re-upload it or send me the file?
Thanks & Regards

Mi, 27.06.2018 - 20:00

When placing both measures in a Pie Graph visual - it defaults to their measure names(Top X, Other), instead of the product name. When it's just the Top X measure, it shows the correct product name. How do we handle correct this?

Fr, 31.08.2018 - 17:01

To be honest, I had the same problem, did not even try to figure out how cos I couldn't be bothered.

Make new measure:
All = IF([TopX] = BLANK(), [Others], [TopX])

Then just use that measure in your chart.

Lukas Lötters
Fr, 02.11.2018 - 17:49

Sorry for my late reply.

the pie chart is a little tricky. To get it working you have to create combined Measure for example like this Sum of Top X and Other = [Other] + [Top X]. Now you can use this measure in a pie chart combined with your categorical field.

Hope it works.

See here:

Regards Lukas

Di, 03.07.2018 - 10:12

Hi, this a very good post. I am new to Power BI but I get it working. Now, I would like to get the totals for TopX and Others in a Card or Multiple Card. How can I do that, I cannot figure out. Thanks :)

Lukas Lötters
Fr, 02.11.2018 - 17:37


the easiest way should be to
1. create a card in Power BI
2. add the Other or Top X Measure
3. add your created categorical element to the visual filter (here ProductName[ProductName] on that card
4. filter to the element Others (or in the case of Top X to not Others)

Hope this works

Here a link to a screenshot

Do, 12.07.2018 - 21:44

Hi Lukas,
The link to Power BI file doesn't work anymore. Could you please re-upload it or send me the file?
Thanks & Regards
Adán Vázquez

Senthil Kumar
Mi, 05.09.2018 - 13:54


I am unable to find the link itself.
Could you please upload the file again?

@Adan if you have the fine, could you send me please?


Lukas Lötters
Fr, 02.11.2018 - 17:50

Hi guys,

somehow the link was lost or stolen i don't know.

I added a new link in the post or you can just use this https://bit.ly/2yMh2tR

Do, 25.10.2018 - 13:05

How can we include a PY measure for Top 10 from current Year?

Lukas Lötters
Fr, 02.11.2018 - 17:56


this highly depends on your data model. You need a date table for this. And then you can create a measure that looks like this CALCULATE([Top X];SAMEPERIODLASTYEAR(DimDate[Date])).

The important thing is that you have a time context, so that the sameperiodlastyear function knows, from which year you want to shift the time context. The easiest way is to add a slicer or a filter where you set the year on for example 2018.

Hope that works.

Di, 08.01.2019 - 13:55

Hi Lukas

Could you please upload the file again?


Lukas Lötters
Di, 08.01.2019 - 17:22

Hi Jorge,
link should work again now.

Fr, 11.01.2019 - 05:56

Hi Lukas,
my requirement is similar to above one,
customer has 20 products, out of 20, 2 are default products, default products always should come, means take top 5 products out of 20, if the two default products are in top 5, then top 5 and rest 15 as other category, if default two products are not in top 5 then top 5 , default 2, rest should come in Others category.
we should display in Pie Chart, please help me here.


Lukas Lötters
Mo, 11.02.2019 - 13:06

Hi Venkat,

I'm not quite sure if I can fix that so easy from remote. Maybe this formular will help but I cannot guarentee. You have to chance the filter conditions so that not only the Top X Products are selected. Maybe this will give you an idea.
[ProductName] = all ( ProductName[ProductName] )
&& or(CONTAINS (
ALL ( ProductName[ProductName] );
"Sales"; CALCULATE (
[Sum of Sales Amount];
FILTER ( DimProduct; DimProduct[ProductName] = EARLIER ( [ProductName] ) )
[Sales]; DESC
ProductName[ProductName]; VALUES (ProductName[ProductName] )
); DimProduct[ProductName] in {"Road-250 Red, 44"}


Do, 30.05.2019 - 16:51

Hi Lukas,
Thank you so much for the good tutorial. I have one problem and I hope you can help me.
I have 2 columns: Budget and Gap. Top 10 Budget / Rest Budget is working fine. Now, I would like to add the 10 Gap that corresponds to the top 10 Budget / Rest Gap. The result I have is:
a. Top 10 Budget is correct;
b. The 10 Gap corresponding to the top 10 Budget is correct;
c. The Rest Budget is correct;
d. The Rest Gap is incorrect.

The problem is that Other Gap (d) is equal to the [total Gap – top 10 Gap]; while, what is needed is [total Gap – the 10 Gap corresponding to the Top 10 Budget (and not top 10 Gap)].

How can I manage this? I can share my file if easier. I appreciate your advice. Many thanks.

Di, 09.07.2019 - 14:03

Thank you for this solution. I have a bit of an issue. So my N is not variable, I just want to show top 25 of say Products by revenue with Others in a grid. However, I would also want to show the quantity as a value column next to the Revenue column. Because there is no relationship between the table and the data model this is proving difficult. Is there any way of doing this?

Neuen Kommentar schreiben

Der Inhalt dieses Feldes wird nicht öffentlich zugänglich angezeigt.


  • Keine HTML-Tags erlaubt.
  • HTML - Zeilenumbrüche und Absätze werden automatisch erzeugt.
  • Web page addresses and email addresses turn into links automatically.
Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden