Dynamic TOP N and Others in Power BI

[@markusher1695]

On the plus side, very good video. Well done. On the negative, this shows just how much like pulling teeth it is to perform the simplest types of analysis in Power BI. Creating a Top N + Other table or chart in Tableau takes about 30 seconds.

[@BIWay]

Thank you for your feedback. Yes you are right, it's a simple type of analysis but it's not obvious to do it in Power BI and not like in Tableau for example

[@Orestes728]

I have yet to meet a person who really rides for Power BI. I was hired, trained, and certified in Tableau but our company pivoted to Power BI. Realizing something as simple as reordering columns requires conditional formatting or invisible ranks is mind-numbing.

[@spencehutchinson1904]

@MicrosoftPowerBI thank you for making this so incredibly simple.

[@user-kr8cg6ld7f]

Excellent Video! Thanks for the guide. 2 Quick things: 1. The table seems to sum only the top 5 in the total row - how can we get this to include the other row value as well? 2. The data should be sorted descending by customer with other at the bottom, could you provide any insight on how to do this? Thanks!

[@ameenabano2776]

Did got solution for sorting in descending order and other at bottom?

[@Crocodilo49]

Worked great for me! Thanks for the tutorial.

[@BIWay]

Thank you for your feedback

[@thehappyapy]

When I make the TopN table and try to create a many-to-one relation with my dimension table, I get the an error that "A circular dependency was detected." Do you have any advice on how to create a Top N table using the dimension table and create a relation back to the original dimension table without creating a circular reference?

[@TheJrim86]

did anyone else notice that the total value using the TopN table is the value of the top N categories and the 'Other' is not included in the table total? ie. on the left, total is 489M, on the right, total is 243M and the difference is what's in the 'Other' category. How can we get these 2 to sync?

[@xC2x-om3hl]

Does anyone know how to fix this? im having the same issue on mine

[@xC2x-om3hl]

That total on the right if you add it up counts everything except "Other". If you add it all manually you get the full total to match the right but the "Total" doesnt show that for some reason

[@lov2tango2]

Same issue with mine. This solution is simple to implement. I used for other visual, not on tables.

[@sreevyshnavigangeyedula4729]

@@lov2tango2 what's the solution for this?

[@TheBeast-ge6uz]

This video was really helpful. Thank you so much. 🇮🇳😊

[@BIWay]

Thank you for your feedback I'm glad it helped you

[@user-eco2023]

Excellent! Thank you very much!

[@BIWay]

Thank You for your feedback

[@MatBat__]

Very cool, thank you man. Subbed

[@BIWay]

Thank you for your feedback

[@blancaoteo6808]

Thank you for the tutorial! the issue I am facing is that when you place the new measure in a table the sum of the table is not correct because it is only considering TopN. I think it is related to the fact that in the original table there is no other value. Do you know how to solve this?

[@venkatrathnam2305]

Thank you soo much it's working fine for my senarion but there is a issue the others it's getting middle some times first when the values is higher but how to sort that can you please suggest

[@saniafarheen9085]

Can anyone let me know if this works if I select for a particular period from the slicer as well dynamically??

[@duttashyamal]

Very well explained video, thank you! Just one question, is there a way to keep the Others category always at the bottom? Also, when we sort the values, the sorting order shouldn't be disturbed post applying a filter. I have achieved the part of the solution you have demonstrated, but not finding a way to keep others at the bottom or if I can keep others at the bottom too, not able to keep the sorting order same post applying a filter 😢

[@BIWay]

Hello, thank you for your feedback. about your question, when you say keep others at the bottom, do you want to sort by Sales Amount all categories except "Others" which will be displayed at the bottom?

[@noobieduo5861]

@@BIWay Yes. I was trying to do the same but do not know how. Can you help to guide on this? Usually in our reports, Others category is always at the bottom of the list regardless of the $.

[@alenmalonzo740]

@@BIWay looking for your solution here for this one

[@celsianairemd2685]

@@BIWay , any solution to this question? Thank you!

[@RamanKumar-c1p]

very good video well done!!

[@BIWay]

Thank you for your feedback

[@la2793]

Thanks for the video! What should I do if I would like to display, with a tooltip or a drill through, the names of the subcategory in "others". I would like to click on others and being able to see which are my subcategories. Thanks

[@BIWay]

Thank you for your feedback, what you can do to display the name of the subcategory in Other is create a measure that concatenates all the subcategories and display the measure in a visual table for example. Here is the DAX that you can use, please try it and let us know if it answers your question (you can check this link to see a screenshot of the measure github.com/BIWay/Power-BI-tutorial/wiki/Example-of-measure-to-display-Subcategories-in-%22Other%22-in-a-visual-table) Other values = var TopNValue = 'Top N'[Top N Value] Var TopPSubCategory = TOPN( TopNValue, ALLSELECTED(TopSubCategory[ProductSubcategory]), Sales[SumSalesAmount] ) Var Others = EXCEPT(ALLSELECTED(TopSubCategory[ProductSubcategory]),TopPSubCategory) Var OthersWithSales = FILTER(Others, Sales[SumSalesAmount] 0) return CONCATENATEX( OthersWithSales, [ProductSubcategory] & " " & FORMAT(Sales[SumSalesAmount], "#,#.## $") , " ", [SumSalesAmount], DESC )

[@marksaba80]

Thank you for this tutorial! How can I modify this to allow me to pull in additional columns and metrics? For instance, I have created my formula like yours to show me the Top 10 Products by Sales with an Other row. This works. However, if I try to pull in additional item details, such as Product Category, the table no longer provides the same data. Additionally, I cannot pull in other metrics, such as Inventory On Hand for the Top 10 SKUs. Do you know how to resolve this?

[@czarinamaegaspar3093]

OMG Thank you! this F'n saved me WTF

[@karlykar21]

Great stuff, thank you so much! This solves a problem for me! Can I please add something? So the table with "Others" is not taking that number into the total. I saw in a forum a discussion about this. I am not experienced, so I really am not sure how these things are interacting. Anyway, what I added to solve this, after "Return" is: IF ( ISBLANK (currentsubCategory), TopNSales + OtherSales, IF( TopSubcategory "Other", TopNSales, OtherSales ) ) I hope I had the variables/table names correct since I was using my onw dataset, and hope it helps. Thanks again for this video!

[@BIWay]

Hello Karla, Thank you for your feedback and your explanation. Indeed, in the video the total does not take into account the value of the OTHER category and by adding your Dax formulat after "Return" we can have the correct total

[@claudensana6790]

Hi @@BIWay and @KarlaPeña, Thanks for your helpful comments. For my project, I need to do a running total in Power BI without using dates and I also have an incorrect total issue, and I don't know how to solve it. Considering the data used in this video and the table on the left (so not the one with the "Top N sales"), I use this function to rank all the sales : Rank PSC = RANKX ( ALLSELECTED(ProductSubcategory[ProductSubcategory]), Sales[SumSalesAmount] ) After this, I use this function to do a running total on the sales : RT Sales = CALCULATE( [SumSalesAmount], TOPN( [Rank PSC], ALLSELECTED(ProductSubcategory[ProductSubcategory]), [SumSalesAmount] ) ) So that enables me to have a running total column for the sales, but the only problem is that it shows an incorrect total (the first value of that column) and I don't know why. Do you have any solution to solve this problem ? I hope I was clear enough. Thanks again @BI Way for your video and @KarlaPeña for the comment.

[@YoutubeEnthusiast1000]

@Karlykar21 Does your code have an error? I think TopSubcategory should be replaced with currentsubCategory. Looks to be a very helpful addition i was having trouble with the totals! thank you!

[@karlykar21]

@@KZbinEnthusiast1000 - I can'rt remember! Need to get back into PowerBI. But yes, there were some tweaks - and probably they had more to do with the PowerBI version and changes than the original video being wrong!

[@juliakruchun2810]

Thank you so much! You helped me a lot!!!

[@udayshankarnellore]

Am using this TopN+Others in Column Chart but I want to show Last Six months data dynamically when I change Month Year slicer. I tried but this TopN+Others is not working. Please help with solution for this

[@pk7249]

Nice. But how to force "Other" category to be always last?

[@MA-tk9kh]

ZZOther

[@jiazeli5606]

Hi, what data type is your [sum sales] column? I tried to add it to the DAX but it errored out saying "add calculation to this column"

[@BIWay]

Hi, the data type is fixed decimal number and the format is currency Can you give us more details about the error, thank you

[@welcometomathy]

Hi instead of "Others" can we make "overall" and give it the average of all?

[@borisev1097]

Great work! Would you share the file to get familiar in depth how it is working?

[@BIWay]

Thank you, You will find the file in this link github.com/BIWay/Power-BI-Files

[@borisev1097]

@@BIWay Thank you!

[@BenceTasnadi]

Thank you for the example file and for the excellent tutorial!

[@abbydimitrakis8261]

Is there a way to show this same layout of information, but show it month to month?

[@BenceTasnadi]

What measure exactly is Sales[SumSalesAmount]?

[@TheBeast-ge6uz]

If I am not wrong it is "SumSalesAmount=Sum(SalesAmount)" where SalesAmount is a column on which we are calculating TopN.

[@manojpraveen5976]

How to rank it so that others always stay at last. Please someone help me on this.

[@mariarazdelkina7920]

Please reply how to fix circular reference error - this makes whole exercise useless

[@SNagaraja-nh5kx]

can you send me this PBI file?

[@kernelab]

great video, but so farking tedious do something so fricken basic. why tf can powerbi not use a measure as a legend

[@faridfarid7070]

but for the Grand TOTAL in the table why is changing ?? when you change selecting top N list ! it should gives total sales of top n categories + total of the rest as Others Category ! the grand total in the the table must be the same 489949.580 $

[@jameshungerford4403]

Noticed the same thing. :) The sum on the total appears to be excluding the "other" category. Any responses or ideas to resolve that issue? PBI can make us look foolish sometimes.