Power BI – Dynamic TopN + Others with Drill-Down

Posted on by — 48 Comments ↓

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

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

    Reply ↓

    • 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

      Reply ↓

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

        Reply ↓

  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?

    Reply ↓

    • 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)

      Reply ↓

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

    Reply ↓

    • 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

      Reply ↓

      • 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

        Reply ↓

          • 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

            Reply ↓

            • 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

              Reply ↓

  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!

    Reply ↓

    • 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

      Reply ↓

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

        Reply ↓

        • 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

          Reply ↓

    • 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

      Reply ↓

  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!

    Reply ↓

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

      Reply ↓

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

      Reply ↓

      • 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

        Reply ↓

  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

    Reply ↓

  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

    Reply ↓

    • 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

      Reply ↓

      • 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])

        Reply ↓

  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.

    Reply ↓

    • 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

      Reply ↓

  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

    Reply ↓

  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.

    Reply ↓

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

      Reply ↓

  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.

    Reply ↓

    • 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

      Reply ↓

  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!

    Reply ↓

  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?

    Reply ↓

  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

    Reply ↓

    • 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

      Reply ↓

  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

    Reply ↓

Leave a Reply