14.12.2016

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
  6. RETURN
  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
  6. RETURN
  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

Conclusion:

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.

Update

You can try it out right here:

Download the file.

Kommentare

Bao
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!
Bao

Steve
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.

Steve
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.

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

Lukas

Mihai
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

Caitli
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?

WeakBI
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:
https://bit.ly/2OmmZm5

Regards Lukas

LLS
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

Hi,

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
Regards,
Lukas

Here a link to a screenshot
https://oraylis01-my.sharepoint.com/:i:/g/personal/l_loetters_oraylis_d…

Adan
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

Hello,

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?

Thanks,
Senthil

Lukas Lötters
Fr, 02.11.2018 - 17:56

Hi,

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.
Regards,
Lukas

Neuen Kommentar schreiben

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

Klartext

  • 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