Power BI – Dynamic TopN + Others with Drill-Down

A very common requirement in reporting is to show the Top N items (products, regions, customers, …) and this can also be achieved in Power BI quite easily.

But lets start from the beginning and show how this requirement usually evolves and how to solve the different stages.

The easiest thing to do is to simply resize the visual (e.g. table visual) to only who 5 rows and sort them descending by your measure:

This is very straight forward and I do not think it needs any further explanation.

The next requirement that usually comes up next is that the customer wants to control, how many Top items to show. So they implement a slicer and make the whole calculation dynamic as described here:
SQL BI – Use of RANKX in a Power BI measure
FourMoo – Dynamic TopN made easy with What-If Parameter

Again, this works pretty well and is explained in detail in the blog posts.

Once you have implemented this change the business users usually complain that Total is wrong. This depends on how you implemented the TopN measure and what the users actually expect. I have seen two scenarios that cause confusion:
1) The Total is the SUM of the TopN items only – not reflecting the actual Grand Total
2) The Total is NOT the SUM of the TopN items only – people complaining that Power BI does not sum up correctly

As I said, this pretty much depends on the business requirements and after discussing that in length with the users, the solution is usually to simply add an “Others” row that sums up all values which are not part of the TopN items. For regular business users this requirement sounds really trivial because in Excel the could just add a new row and subtract the values of the TopN items from the Grand Total.

However, they usually will not understand the complexity behind this requirement for Power BI. In Power BI we cannot simply add a new “Others” row on the fly. It has to be part of the data model and as the TopN calculations is already dynamic, also the calculation for “Others” has to be dynamic. As you probably expected, also this has been covered already:
Oraylis – Show TopN and rest in Power BI
Power BI community – Dynamic Top N and Others category

These work fine even if I do not like the DAX as it is unnecessarily complex (from my point of view) but the general approach is the same as the one that will I show in this blog post and follows these steps:
1) create a new table in the data model (either with Power Query or DAX) that contains all our items that we want to use in our TopN calculation and an additional row for “Others”
2) link the new table also to the fact table, similar to the original table that contains your items
3) write a measure that calculates the rank for each item, filters the TopN items and assigns the rest to the “Others” item
4) use the new measure in combination with the new table/column in your visual

Step 1 – Create table with “Others” row

I used a DAX calculated table that does a UNION() of the existing rows for the TopN calculation and a static row for “Others”. I used ROW() first so I can specify the new column names directly. I further use ALLNOBLANKROW() to remove to get rid of any blank rows.

Step 2 – Create Relationship

The new table is linked to the same table to which the original table was linked to. This can be the fact-table directly or an intermediate table that then filters the facts in a second step (as shown below)

Step 3 – Create DAX measure

That’s actually the tricky part about this solution, but I think the code is still very easy to read and understand:

Step 4 – Build Visual

One of the benefits of this approach is that it also allows you to use the “Others” value in slicers, for cross-filtering/-highlight and even in drill-downs. To do so we need to configure our visual with two levels. The first one is the column that contains the “Others” item and the second level is the original column that contains the items. The DAX measure will take care of the rest.

And that’s it! You can now use the column that contains the artificial “Others” in combination with the new measure wherever you like. In a slicer, in a chart or in a table/matrix!

The final PBIX workbook can also be downloaded: TopN_Others.pbix

85 Replies to “Power BI – Dynamic TopN + Others with Drill-Down”

  1. Hi there. I have been searching for how to do this with a tabular cube as my data source. I haven’t had any luck. Would you know how to implement, say top 10 and Others on a SSAS tabular cube?

    • Well, the approach is the very same as described in the blog post. Basically it is just DAX
      However, you would need to know in advance for which table/column you want to calculate the Top N

      • Hi Gerhard,

        Thank you for your response. Does that mean that I would have to perform that first step (create table with others row) in SSAS? Because in PowerBI desktop, the new table and new column icons are greyed out for me. I am learning Power BI on the fly so apologies if some of my questions aren’t very smart.

  2. Pingback: Dynamic Top N in Power BI – Curated SQL

  3. Pingback: Power BI App Nav, TopN, Report Server, Perf and more... (May 27, 2019) | Guy in a Cube

  4. Good idea! One question… the last return part, can’t we just do:
    SUMX(ItemsFinal, [RankMeasure])

    This way we avoid recomputing the Measure using Treatas?

    • yes, that would of course also work and could potentially be faster – but you should test this on your dataset!
      I just try to keep the formula generic so it does not just only work with SUM measures but basically with any kind of measures (e.g. think of DISTINCTCOUNT or AVERAGE)

  5. Hey Gerhard, Thank you for this blog post! I wanted to know how can you make the surrounding visuals interactive with the “Others bar/slice”. For instance if when I select “Others” from the visual then I would want the surrounding visuals to filter based on the selection of “Others”.

    • well, you need to use the new measure (in my example [Top Measure ProductSubCategory]) also in the other visuals
      then they should also get filtered accordingly

      you can just download the sample .pbix at the end of the post and have a look

      -gerhard

      • Here my issue. Say you wanted the selected measure to be Order Count. So you create a measure OrderCount = DISTINCTCOUNT(‘Reseller Sales'[SalesOrderNumber]) . When you try to use the “Top Measure ProductSubCategory” with the OrderCount measure as the selected measure the surrounding visuals don’t calculate correctly.
        Link to screenshot: https://drive.google.com/file/d/0Bx8H0lmz4IM4c1VBLWdidVZTNHQzNmZkdkc5Zk5zMUk4R3Z3/view?usp=drivesdk

        Link to pbix zip: https://drive.google.com/file/d/0Bx8H0lmz4IM4TmRyaTJGN2x0MFBhaElGd216OEtCWkNqOWo4/view?usp=drivesdk

          • Thank you Gerhard for looking into it. Note: Subcategory “Mountain Bikes” is not the only subcategory that is off. The “Others” subcategory are also off on the BusinessType table.

            The “Others” subcategory bar calculates to 3368. While the BusinessType table when the “Others” bar is highlighted makes up a “false” total of 3319 when adding each BusinessType line item.

            Brandon

            • ok so the problem is the following:
              the measure is evaluated in the current context of each single Reseller’s Business Type.
              for the Business Types “Warehouse” and “Value Added Reseller” the Subcategory “Mountain Bikes” is not in the Top 3 Subcategories of this Business Type hence it does not show a value if you filter globally for “Mountain Bikes”. For these two Business Types the Top3 Subcategories and “Mountain Bikes” are exclusive hence you see no value
              to work around this, you would need to add ALL(Reseller) to the measures variable “ItemsWithValue”
              VAR ItemsWithValue = ADDCOLUMNS(Items, “RankMeasure”, CALCULATE([Selected Measure], ALL(ProductSubcategory), ALL(Reseller)))

              so the calculation does what it is supposed to but simply does not match your requirements as you need to specify exactly in which context you want to calculate the Top3 Items

              -gerhard

  6. Hey Gerhard,
    first of all, thanks a lot for your detailed post!
    I have two question regarding your post:

    1. I tried to reproduce all your steps on my data set and all steps seem to work correctly until it comes to the visuals: Here, the “Others” Bar is never shown. Do you have an idea what the reason for this issue could be?

    2. I had a look at the variable “Items” in your pbix file and I just don’t understand where the blank row on the bottom of the column comes from (since your original data do not contain any blanks).

    Thanks a lot in advance. I highly appreciate your help!

    • Hi Benedikt,
      regarding 1. – just make sure that you use the new table and not the original table that contains your items (or whatever it is in your case)
      regarding 2. – well, if your original table contains a blank row, then also the new table which contains the additional “Others” item also contains the blank row

      -gerhard

      • Hello Gerhard,
        Thank you for this post. I have followed all the steps but I have the same problem: the “others” category is never shown, even though I am using the new table.

        • Hi Natt,

          Did you also use the new measure?
          it only works if you use the new table AND the new measure

          otherwise send me the file and I will have a look

          regards,
          -gerhard

    • Hi,

      I had the same issue:

      1. I removed this condition on the final filter.
      CONTAINSROW(VALUES(ProductSubcategory[SubcategoryName]), [RankItem])

      All it seemed to do was filter out others from ItemsFinal.
      You shouldn’t have to filter anyway as the selected measure should be accounting for any existing filters on the base table.

      This means the subcategory is still listed but will have a zero value rank measure, would be ranked at the bottom and grouped into “Others”.

      Hiding values with no data on the visualisation should remove them from drill down if you want to do that.

      2. The blank row seemed to be introduced in the first virtual table:
      VAR Items = SELECTCOLUMNS(ALL(Subcategory_wOthers), “RankItem”, Subcategory_wOthers[SubcategoryName_wOthers])

      Even though SubcategoryName_wOthers had no blanks, the Items table did have a blank. No idea why.

      I replaced it with:
      VAR Items = SELECTCOLUMNS(ALLNOBLANKROWS(Subcategory_wOthers), “RankItem”, Subcategory_wOthers[SubcategoryName_wOthers])

      And that seemed to work with no issues. Again, no idea why.

      Thanks
      Will

  7. This is really great. I figured out how to sort the visual with Others always sorted to the bottom. I did this by creating another [TopOtherRank] measure almost the same as your original, but with the RETURN part being
    IF( HASONEVALUE( Subcategory_wOthers[SubcategoryName_wOthers] ), FIRSTNONBLANK(SELECTCOLUMNS( ItemsFinal, “Rnk”, [Rank] ), TRUE() ), BLANK() ). Might be useful to someone!

    • David,
      I do need the Others group sorted to the bottom no matter what, thx for posting your code but not sure
      How exactly your code chunk above will fit into the original measure? Since ItemsFinal doesn’t have the [Rank] column?

      If you don’t mind, could you post the complete measure code?

          • We are wondering where exactly to insert this in the original code?? IF( HASONEVALUE( Subcategory_wOthers[SubcategoryName_wOthers] ), FIRSTNONBLANK(SELECTCOLUMNS( ItemsFinal, “Rnk”, [Rank] ), TRUE() ), BLANK() )

              • from David Cadman on 2019-10-30 at 16:09 said:
                This is really great. I figured out how to sort the visual with Others always sorted to the bottom. I did this by creating another [TopOtherRank] measure almost the same as your original, but with the RETURN part being
                IF( HASONEVALUE( Subcategory_wOthers[SubcategoryName_wOthers] ), FIRSTNONBLANK(SELECTCOLUMNS( ItemsFinal, “Rnk”, [Rank] ), TRUE() ), BLANK() ). Might be useful to someone!

                P.S
                Is there a way to increase the performance of your original code in the Top Measure SubProductCstegory measure?

                • proper ranking is definitely a great addition – though it will further slow down the query as it also has to evaluate the ranking measure
                  in general, the measure is already quite compute-intense due to the sorting it has to do internally. Especially when the number of your items is large (5000+) this can become problematic but I have not found a way yet to improve the performance, sorry

              • From David Cadman on 2019-10-30 at 16:09 said:
                This is really great. I figured out how to sort the visual with Others always sorted to the bottom. I did this by creating another [TopOtherRank] measure almost the same as your original, but with the RETURN part being
                IF( HASONEVALUE( Subcategory_wOthers[SubcategoryName_wOthers] ), FIRSTNONBLANK(SELECTCOLUMNS( ItemsFinal, “Rnk”, [Rank] ), TRUE() ), BLANK() ).

    • So this is probably a stupid question… I was able to create the order measure, but how do I implement this for a matrix visual?

      • Hi Dan,

        depending on your matrix visual, if you have column fields, it does not support sorting. But this has nothing to do with the DAX measures described here but only with the visual you use

        kind regards,
        -gerhard

  8. Hi David,
    Please the TopN is not interacting with my Visual.
    I have checked and double – checked my codes, I can’t place my hand on the error. Please I would appreciate your kind assistance.
    Best Regards,
    Eghosa

  9. Hi Gerhard,

    Nice post.
    1.The link to your ‘TopN_Others.pbix’ no longer works.
    2. If I wished to add an additional slicer that allowed the user to show either TopN or BottomN values … How would I do this?
    Thanks

    • Hi Steve,

      just tested the download and it worked just fine for me as it is – can you try again please?
      If you want to have a dynamic TOP/FLOP N via a slicer I would probably add a new table with values
      TopFlop | Multiplier
      TOP | 1
      FLOP | -1

      create a measure TopFlop_Multiplier=MAX(TopFlopTable[Multiplier]) and then add this measure to the original formula.
      The slicer will then simply reverse the ordering within the calculation and allows you to easily switch between TOP and FLOP N

      kind regards,
      -gerhard

      • Thanks for the prompt reply.
        1. Download works in Chrome, and Edge but when using Firefox it tries to save as a text file. No idea why.
        2. Where in the original formula do I add the measure?: TopFlop_Multiplier=MAX(TopFlopTable[Multiplier])

  10. Gerhard,
    First of all, thank you very much for this article and for publishing your solution.
    I’m playing with your pbix and trying to understand the measure. I added a slicer on ProductSubcategory[SubcategoryName] = Mountain Bikes, Mountain Frames, hereafter (MB, MF).

    I particularly don’t understand the “ItemsFinal” step within the measure.

    When I try to debug the table variable ItemsFinal in DAX Studio with the filter on ItemsWithTop commented out completely, it has the same results as when I evaluate ItemsFinal with the original code. Yet, the results of the measure are different. Why is this?

    What’s particularly strange to me is the table variables are derived from Subcategory_wOthers and in this step you are using that same table as filtering criteria. I suspect I don’t fully comprehend how table variables behave.

    You try to explain what this line of code does, but can you expound on this?

    CONTAINSROW(VALUES(Subcategory_wOthers[SubcategoryName_wOthers]), [TopOrOthers]) /* need to obey current filters on _wOthers table. e.g. after Drill-Down */

    I believe VALUES(Subcategory_wOthers[SubcategoryName_wOthers]) in this slicer context are MB and MF, so when we filter TopOrOthers only the ‘MB’ row will be found because MF has a value of “Others”, right?

    What part of the code handle the calculation for the Others subtotal?

    I understand the final step with the TreatAs to be, “whatever RankItems are selected in ItemsFinal, apply that filter to Subcategory_wOthers[SubcategoryName_wOthers]”. I suppose in my scenario, RankItems has to be MB, MF, and Others?

    Thank you very much for the clarification, sorry for all the questions.

    • Hi Dan,

      If I remember correctly, this was necessary in case you drill-down from Subcategory_wOthers to Subcategory. Without the filter it would produce wrong results because some filters would be overwritten.

      kind regards,
      -gerhard

  11. Good Day Gerhard,

    Thanks for this post!

    Your solution works perfectly on both Top N and Others & Bottom N and Others. However visual refresh is toooooo slow about 18 secs every time we click on drill down.

    Data is huge, I need Top 10/ Bottom 10 Customers and Rest in Others. Customer list is 250,000 and Actual data is huge. Client want Area wise Top/Bottom 10 in Matrix.

    I searched for similar solutions works perfectly and faster but only Customers are used, if add Area in Matrix doesn’t give perfect results.

    Any thoughts?

    Appreciate your help on his

    Regards
    Nasir Shaikh

  12. Hi Gerhard,
    Thank you for share your knowledge, the code work perfectly but when i add the column year, the filter hasn’t work, i need help in this case. I want to represent all years in the same table.

    • actually, If you simply place “Year” on an axis of your chart (or “on columns” of your table) the code should just work – at least if “Year” comes from a separate table that is only linked to your facts
      is this the case or does your Year-column belong to the TopN dimension or the facts?

  13. Hi Gerhard,

    Thank you so much for sharing such a great article, I got it from Curbal YouTube channel. your code worked for me, but I have a question, now If I my selected Measure “Order Quantity” is associated with another column “Unit of Measure” for different products. so I have some Products have unit of measure in Liter, others have unit of measure in KG. how to get the TOPN + Others for each specific unit of measure?

    Regards,
    Mohamed.

    • well you would need a dedicated column which contains the unit only (e.g. “liters”, “KG”, …) and then simply use it in a filter

      this should work but may also depend on your data model and table structures

  14. This is an amazing post! I have a model with lost of inactive relationships, so my end result got much longer, but I made it!

    I was able to debug it easily by running ADDCOLUMNS steps of the measure as a calculated table, and putting the variable for each step in the return. Allowed me to find out where I was going wrong – and get it fixed. ?

    Thank YOU!

  15. This works for my top 20 Institutions but does not populate for others. I think it is because I am using a matrix and have the Year in the columns. Is there a filter or summary I can add to populate the others and leave the Top 20 institutions the way they are?

  16. Hi Gerard, thank you for the lovely post, I have a similar requirement if you could please help
    1) Show the selected items in the slicer at the top of the report
    2)disable the drill down for all but others

    • To show the selected items you can use a DAX measure
      Drill down cannot be disabled but you could try to set the value to Blank() if the SELECTEDVALUE() of both levels match

  17. Hi Gerhard,

    This is amazing, very helpful thank you so much.
    Thanks to Curbal for introducing “blog.gbrueckl.at” to me.

    Regards
    Majid H. Baluch

  18. Hi Gerhard,

    Thank for your great post.

    It would be grateful if you could help me to make hierarchy Top N and other.
    In top N/others category, it should be able to drill down.

    Any help is highly appreciated.

  19. Hi there, great post.

    I am trying to show top n items but also always include 1 or 2 specific items.

    i.e.
    Company A
    Company B
    Rank 1 (or 2/3 if 1 is A or B)
    Rank 2 ( or 3/4 if 1 or 2 is A or B)

    Many thanks if you are able to help

    • You can try to add another sort-epxression within RANKX which is 1 for Company A and Company B and 0 for all other companies
      if you use this as first sort-criteria in RANKX and the actual measure as a second, it should always return the two companies following the top N others

      regards,
      -gerhard

  20. Hi Gerhard,

    I really like this solution, thank you for posting it and actively replying. I noticed up in the comments, some one else had a similar issue that I am facing. Everything seems to work, except on my visual I never see the “Other” grouping. I have chosen my “topN” dimension as the axis in my visual. I have also chosen my new measure as the values.

    I also am only using only the dimension I used to create all of these in my case – product market. I saw above, the person needed to add another dimension to the itemvalues variable.

    Anyhow, I feel a bit stuck. I am happy to send my pbi book to you if you’re feeling charitable enough to take a look. Thank you!

    Eric

      • So interestingly, I was able to get this to work by commenting out the second CONTAINSROW expression in the code that produces ItemsFinal….

        VAR ItemsFinal = SELECTCOLUMNS( /* we can only select a single column to be used with TREATAS() */
        FILTER(
        ItemsWithTop,
        CONTAINSROW(VALUES(ProductMarketTopN[ProductMarketTopN_Name]), [TopOrOthers])), /* need to obey current filters on _wOthers table. e.g. after Drill-Down */
        //&& CONTAINSROW(VALUES(‘edw dimProductMarket'[ProductMarketDescription]), [RankItem])), /* need to obey current filters on base table */
        “TopN_Others”, [RankItem])

        The only difference between my solution and yours is my ProductMarketTopN table hangs directly off of my fact, rather than filtering a dimension that hangs off of the fact. Do you think this has anything to do with needing to remove that line?

        I would think the expression I removed would in fact filter rows that had “Others” because my base dimension does not contain the value “Others”. But on the other hand, neither does yours.

        I would love any input you’re able to provide. I don’t like just removing things and saying *it works*without understanding 🙂

        Thanks again!
        Eric

        • It at least appears that removing that row has removed my ability to “drill down” into the “Others” category… So there’s that. 🙁

          • the calculation is in this case is very specific to the data model so if your data model is only slightly different, it may not work.
            Is there any particular reason why you cannot use the same data model as I did in the post?

            -gerhard

            • Hi Gerhard,

              Thanks again for working with me on this.

              Your solution was elegant and simple enough, that I did not see a need to recreate the same thing in the same data model, just to prove I could. I am delivering work to a client that wants this functionality, so I built it using their data and model.

              That said, I have solved the problem. It appears this solution relies pretty heavily on the top n dimension being an outrigger or snowflake off of a dimension that filters the fact, rather than the top n dimension filtering the fact directly. I understand better now why. After making my model do it this way, everything works exactly as yours does.

              Thanks again for an awesome solution and chatting about it.

              Eric

  21. Hi Gerhard,

    First of all thank you for your blog.

    I wanted to implement the dynamic TopN + Others on one of my data sets and have followed step by step the process in your blog.

    However, the “Others” bar is not appearing in my charts / visualizations.

    After reading the comments on the blog, I tried removing “&& CONTAINSROW(VALUES(ProductSubcategory[SubcategoryName]), [RankItem])” from the ItemsFinal code to check if it works. This worked only when there are no slicers. However when I introduce a slicer, the coding does not work.

    I have spent hours trying to figure out my mistake but am unable to do so.

    I relatively new to PowerBI and have a simple dataset.

    What do you think the error could be? Would it be possible to send the .pbix file to you for a quick look.

    Appreciate your help.

    Kind regards,
    Mufadhal

  22. Hi

    I am having similar issue in my report where I have to find out Top5 and Others in a piechart. Thing is that I am unable to get the totals for Others using your approach.It is showing 0 as value for Others but i should be getting value excluding Top 5

    Regards
    Arun

    • please verify that your data model is set up the same way as the one in the blog – especially with regards to relationships!
      if this is the same and you use the DAX code i provided, I am sure it will also work for you

    • please make sure that your data model – especially relationships! – are the same as in my example and that you also use the same DAX
      you can also download my sample and try to reverse-engineer it

      -gerhard

  23. Is there a way to increase the performance of this measure?

    I used this example for my own visuals using another dataset with multiple columns of this measure in a matrix the performance becomes very slow or doesn’t render at all due to lack of memory.

    • well, the calculation can be quite expensive especially if the number of your items is 10000+
      what exactly do you mean by “multiple columns of this measure”?
      are you combining two TopN calculations?

      -gerhard

      • I’ve created multiple measures using your formula a measure for CurrentYear, PreviousYear, Planned, etc. and insert hem into a matrix the performance is affected and might be causing the visual to exceed the available resource in Power BI Service.

        • again, the calculation is quite compute-intense – if you mix multiple of those measures/columns it will get even worse

  24. Hello Gerhard!
    Thanks for the solution. In my case the programm says that it can not find this:

    [Selected TopN), [RankItem]

    What is it refering to?

    Thanks in advance!
    Regards!
    Oksana

    • I think there was an issue when you copy&pasted the DAX code
      you maybe try to download the sample workbook and try it from there
      The code itself as it is shared here works as it is the same as in the PBIX file

  25. Hello Gerhard!
    Thanks for the solution. In my case the programm says that it can not find this:

    [RankItem]

    What is it refering to?

    Thanks in advance!
    Regards!
    Oksana

    • I just tested it again with the attached pbix file and it works as expected
      also the code in the post is the same as the one in the pbix file

      are you maybe using Power Pivot in Excel?

  26. Hey Gerhard,
    First and foremost, thank you very much for your post.
    I stuck with reproducing you report following all your steps in a simple small data set. Not only does it not show the top N and others in the horizontal axis, it would not apply any filter to the data base on time filter.
    my data set has 4 products in a period of 5 months and a randomly generated sale amount:

    | time | Product | Sale |
    |—————-|————|——- |
    2023-01-05 | a | 100
    2023-01-05 | b | 150
    2023-01-05 | c | 170
    2023-01-05 | d | 130
    —————————————-

    Thanks a lot in advance. I really appreciate your help!

    • I’d recommend to download the PBIX at the end of the post and play around with it/change it to your needs to understand the concepts and have some “playground”
      afterwards I am sure you can also apply it to your final dataset

      • Hello Gerhard

        I am having a similar problem in my report where I have to find out TopN and Other in a bar chart. What happens is that I can’t get the row of Others using your approach, it doesn’t appear in the table. I have reviewed the PBIX that is in the publication and I noticed that my data model is different from the one I have, could that be the error for which does not show me the row Others?? Thank you very much, I will be attentive to your answer.

        Greetings
        Amada

        • well, the logic is very sensitive and expect some specific setup in terms of tables and relationships, so yes, it is probably related to your model being slightly different then mine
          you either need to change your model to match mine or update the DAX so it also works with your model (if its possible at all)

          -gerhard

Leave a Reply to Brandon Cancel reply

Your email address will not be published. Required fields are marked *

*