Filter top 3 products with a row for others using DAX in Power BI

[@claudiocannistraro279]

The value of this video is priceless! Un caloroso GRAZIE dalla Sicilia, Alberto!

[@FruitfulPerspectives]

Excellent! Easy to follow, easy understand, and relatable. I'm amazed at how fast he figured out the Rank+1 miscalculation at 34:00. Great tutorial.

[@niamhstanley4723]

You have just helped me solve a clients requirement I have been trying to fix for over 5 months! WOW thank you!

[@HachiAdachi]

Truly masterful. I love peeking into Alberto's brain and see how all the pieces come together in there. A long video, but worth it! Thank you, Alberto!

[@KA-rk9to]

I loved seeing how even Alberto spends time looking for a missing parentheses and forgets the return part. I somehow feel less of an idiot now.

[@jakitaki3082]

Agree. That's adorable:)

[@ytperson87]

Dear SQLBI team, thank you for your work. Your presentations, like this, are helping me at work.

[@robbieausman1337]

Alberto once again knocks it out of the park. I was asked by my Exec to do this very thing in December 2020, I wasn't finding very helpful instruction elsewhere, Alberto's step by step instruction were great.

[@srinivaskandru5034]

Tension mounts up in a live workshop after a satisfactory search of a good 30 secs for the missing part, experienced the same bermuda spot many times as a trainer. No matter how great you are, it happens for everyone.. I have expected a sophisticated formula for this calculation (coz many new functions were born after 2018 in PBI), but still so many nuts and bolts needed for this Top N products after so many years of product lifespan, I am talking abt Top N. But some DAX patterns were changed i.e budgeting and Basket analysis after the advent of new functions..coz I still remember watching the good old days of Budgeting in Power Pivot and Power BI in the 2015/16, tens of times for good understanding.. Thanks Alberto..meet you one day in India..

[@yiubili1]

That's truly a masterpiece. It's so complicated, yet you make it so understandable! Thanks very much for the video, sir!

[@decentmendreams]

Hi Alberto, this was actually very easy to follow up and the fact that you did it in PBI desktop helped a lot. I followed your instructions and got the results I wanted right away. You are so awesome! Thank you so much!

[@OneNI83]

First it seems like "Mission Impossible",but Alberto cracked it. Excellent.

[@peterbogdanovich4043]

This is better then a Ferrari, it is Sleek and awesome like a Ferrari and yet it is available on you tube for free. Alberto you are GOOOOOOOD!

[@ZappPSR]

Thanks Maestro Alberto for the video. This demo shows how important logic and creativity are to good coding. Knowing DAX combined to logic and creativity as you said can solve any scenario you encounter!

[@haleylearn]

I really like your way of presenting, slowly but without any gaps in knowledge for the audience. Everything is clear little by little, thank you very much for the useful information

[@Bharath_PBI]

This is exactly what I did in my previous project, the small change was, instead of making placeholder for others row in the middle, we placed at the end by counting distinct products +1 , then row visible filter logic was to show all products rank

[@andrewwalker5974]

Another great video from Alberto. Thanks!!! it's pretty awful the lengths we need to go to to work around Power BI's shortcomings. I'm having to use Power BI now because my company has moved away from QlikView. In a QlikView dashboard you can select a Top N as a static or user variable qty and the visuals' properties have a lovely checkbox to "include others" and QlikView just does it - in a split second, no further coding, lots of time saved to do something productive instead.

[@ShabnamKhan-vk7fj]

Thank you so much Alberto, I love how passionate you are about getting to perfection with DAX. Your love for your work is truly inspiring for your students.

[@IoanSendroiu]

Your videos are the best videos I've ever seen. Many many thanks for all your work.

[@marcsimpson6443]

Fantastic Video. So helpful. I watched once, then managed to put together what I needed (Pie Chart rather than matrix) using your technique and it worked perfectly. You explained each step so well.

[@tonandaya7414]

Wow.... excellent ! ... solved what i want to do in my management report , step by step on how to do it ... Perfect !

[@kabilraj746]

Breaking complex requirements into smaller pieces makes it easy to understand. Looking for more videos ..

[@3danim8r1]

WoW......I have seen that video thrice to understand the concept.....Thank you so much for sharing your knowledge👍👍👍....

[@mytubeview]

I been spending hours to try to solve this problem! Thanks for this video!

[@gulhermepereira249]

Bravo! This is pure gold. Thank you for sharing, Alberto

[@AkhtarHussain-ct2hr]

Thank you so much for the excellent video, very well organized and presented. One suggest, if I may make, please please make the size of your curser larger or make it transparent, I had difficulty following where you were clicking.

[@mshparber]

Great, Alberto! I would add a little change: 1. If there are less products that TopN, then no need for Other, so it should return BLANK() and not zero (at least in my scenario). Thanks!

[@JohnSullivan1]

So simple, yet so complex. Excellent Alberto!

[@Ibraheembinmehmood]

This video is so useful. Thank you so much for this. Special Thumbs Up for the way you explained the whole Technique.

[@josepromero1452]

Fantastic Alberto! Thanks a lot for this so useful tutorial. Not only to learn more about DAX, but also for its application to the customers requirements.

[@comunidax968]

Congrats Alberto and team, It is noticeable the production quality.

[@zakarialabidi7520]

Great video! I created a bar chart using the logic. Is there anyway we can drill down on "Others" too see the details?

[@shyambhadresa]

very useful vid! I would like to group up the blank values with the 'Others'. How would I achieve this?

[@davidirias3459]

Greeting from Central America, I'm learning a lot with each video. Thank you

[@TheLeotLion]

Excellent handwriting, great solution ( thank you for the video ! )

[@mohammed333suliman]

As always great stuff, I like the way of providing outlines in the beginning of the video and showing organized breakdown of how you are going to solve the problem. As always great lesson from great teacher. Thank you Alberto Thank you SQLBI team.

[@jameszhou162]

Just practiced it with my dataset. it works like a charm. love the contents and i am reading thru your book as well. thanks.

[@SQLBI]

Good to know it works for real! :) Thanks for sharing it.

[@dariuszspiewak5624]

Alberto, instead of assigning a dynamic rank to the "Others" item, you could take the absolute max from the TOPN table, add 1 to it, and assign this to the "Others" item as the rank. This way you don't have to think too much about the arithmetic and it'll work just as fine. Of course, in the measure that governs the display of the rows you'd need to just add an OR condition to account for the "Others" row. And the ranking could still be used for sorting. The basic idea here is to assign the (max of the TOPN table + 1) to the "Others" item. If you still don't want to think too much, just assign plain 10000 to "Others" as its rank and account for this in the row-filtering measure as before using OR. Sorting will work fine again.

[@SQLBI]

Did you measure its performance? We don't think it would change the performance much, and the code would be very similar - but we might be wrong!

[@engvictorfarias]

Amazing, Alberto. Thanks for sharing your knowledge with us 💕💕🥰🥰. Come to Brazil and bring Marco too. 😁😁.

[@janwillemvanholst]

Great video. Very educational. I think the Top3Prods at 27:00 is unnecessary, it is already down to these top product (and others but the value is zero in this stage).

[@kryptonets]

Truly Genius, Alberto! Thank you very much and Marco for all your work. I started studying DAX two years ago with SQLBI and your patterns. I wanted to ask if it is possible to use MDX inside PowerBI, I sometimes use it creating named sets in Excel and was wondering if you know any features or secrets about it. 🙂

[@juandavila41]

Excelente video, genial la utilización de los parámetros para volver el TOP dinámico y genial la manera como se resuelve por medio del condicional del rankin la fila "otros" para las visuales. Muchas gracias.

[@arnohoedelmans]

Great Video Alberto. I have a question. I add the customer name to the rows. I can't understand why i am getting rows with no sales. What should be changed to not show those rows?

[@dikshasrivastava9125]

Thanks for the video. I have created the table consisting of top 3 product in order of sales but in row total i want to get total sales and not sales of top 3 products. Can you please help regarding this

[@jimjortons]

very nice and informative! linking via product name - is it not risky if two or more products have the same name? would it be possible to add the productid as well in the union creation and use that instead? i am very new to powerbi and still exploring. thank you.

[@MrVishu125]

Hello Alberto, this was awesome and thanks, I have one last question, If I want to display the information in a chart instead of a table, how do I sort the data in a graph similar to the table view. ? please do help me with an answer.

[@vincentdusk2383]

Brill! Thanks for giving that fantastic solution. Not that easy, but I was able to recreate everything and I will present it to my fellow colleagues soon. It is always a pleasure watching your informative and useful vids. Cheers, Vin aka Pia

[@venkataboddapati7356]

Great video from the DAX Guru! I have a question, suppose, I have a Date (Dates Between) slicer on this matrix and I want to get 'Opening Balance' and 'Closing Balance' in the same matrix. Kindly share a video on this as part of this series.

[@SQLBI]

See this: www.daxpatterns.com/semi-additive-calculations/

[@venkataboddapati7356]

@@SQLBI Thanks for the link, It is very helpful. However, I have asked for Opening & Closing balances on your video title "Filter top 3 products with a row for others using DAX in Power BI" (kzbin.info/www/bejne/pIfZnXh-gNeSZtE&ab_channel=SQLBI) context. 🙏.

[@khardu]

OOOhhh ... a heavy dose of DAX! Thank you Alberto, cool report.

[@ezekiel0606]

this is amazing. I thought you were writing it on the spot. regardless, genius

[@emiljanik3078]

Thanks Alberto for very helpful tips. One question about Ranking measure - why you didn't use [TopN Value]+1 instead of 4 in IF( IsOtherSelected, [TopN Value]+1... This way you can filter not only TOP 3 but TOP N products.

[@andrea2686]

Thank you for the insightful video; it was truly valuable. However, I encounter another challenge. I'm working with around 20 categories within my dataset. The complexity arises from the need to dynamically filter the data, with the number of categories being filtered varying each time. Sometimes, I might filter by 3 categories, while at other times, I could require filtering by 5 categories. Is there a way to automatically aggregate the remaining categories as "Others" based on the selected filters, and subsequently visualize this as a pie chart? Your assistance is greatly appreciated. Have a wonderful day ahead!

[@hongngocnguyen8806]

Top notch video! Thanks a lot for this. I have a question tho. Instead of a Whatif table to generate N values, I limited the slicer to show top 5,10,20 and 30, and all worked well. However, I also want to add an “All” option to the slicer, when I choose which all the data will be displayed in the matrix, sorted descendingly. I’ve been trying to solve this for a while but couldn’t. Do you have any suggestion? I’m very grateful for any help.

[@JulieJoerger]

I followed this video perfectly and was thrilled till I realized I need to have a matrix with columns being the last 18 months. When adding the date field and making it a matrix it doesn't seems to work. Is there a way to do Top N and Other with a matrix? And the Top N based on the total of the year, not each month?

[@VaibhavMane784]

Thank you so much sir for this valuable content ❤. Have one question, Suppose , want to find out bottom 5 products then how would it work?

[@SQLBI]

The TOPN function has an argument to specify the order - use ASC instead of DESC.

[@szlim3829]

A very very good video, thanks for sharing your insights and methods on making this customization.

[@brianligat9493]

If so common, why isn't this an automatic feature in PBI? Another great video - thanks!

[@SQLBI]

Ask Microsoft! :)

[@mortens5266]

Thank you for another excellent and educational video! These are really appreciated! After watching the first video on the TopN function in DAX Studio, I had the same thought as the other viewers like you mention in the start of the video 0:49.. Though this video was excellent, what I am "missing" is link between the complex query in DAX Studio and Power BI. How do I, step by step, code piece by code piece, move my query from DAX Studio to a useful measure in Power BI? If you could make a video on this it would take the understanding of DAX for a lot of us DAX developers to a whole new level.

[@Usuario-gk1ni]

Thank you so much... you are a truly teacher

[@benignoguilarte2806]

Alberto you are a master!!! great video, I spent a lot of time thinking about the logic used to solved the problem and this is just excellent, easy to understand once you follow your recommendations. Thank a lot please to continue creating sensational videos as this one

[@rifatxahir]

I have a survey where we asked in the rank of same products as 5 questions - Rank 1 - A/B/C/D Rank 2 - A/B/C/D Rank 3 - A/B/C/D Rank 4 - A/B/C/D Any easy way to calculate these options A,B,C,D on the basis of rank weight?

[@mathew9665]

Fantastic, thank you Alberto - I do agree with another viewers comment. It seems odd that Power BI has a built in ability to do TopN as a filter, but not to give the ability to some the remaining (which is a very standard request) I do wonder how quick this query would be with a dataset with many millions of rows to compute through

[@SQLBI]

Performance is related to the number of products, not on the number of rows in the fact table. If you have 10,000 products the performance should be acceptable, if you have millions of products, probably not.

[@stumpywest1652]

@@SQLBI thank you so much for your reply, of course the fact table is not fully searched, the elements in the queries/measures and visual reduce the rows

[@SQLBI]

It's not that - it's that the smaller number of products reduces the materialization of intermediate results in memory, which is the expensive part. The fact table is always scanned entirely no matter what filter you apply, but having to create a temporary table with intermediate results is what can create performance issues.

[@529deepaks]

Hi Alberto, Thank you for amazing videos. I need one urgent help. I want 3 card visuals in my report. First will show highest selling brand along with sales amount, second will give second highest selling Brand along with sales amount and similarly for third. I tried a lot but no luck. Can you pls guide me here with measure or anything. Thank you!

[@purpleorked]

this is a very helpful video! i'm creating a dashboard which has live connection to a data cube (SSAS), so I couldn't add new table as per the second step. is there any workaround? thanks in advance!

[@SQLBI]

You should be able to create a composite model, even though this impacts performance.

[@purpleorked]

@@SQLBI which means I have to switch to a Direct Query connection and adding local model first, before creating a composite model, am i right?

[@SQLBI]

You could consider a composite model in Power BI, but you might encounter performance issues and you add a refresh required to update the composite model.

[@darkflayer03]

Very useful topic and a better way of understanding the logic of the code. Also, a curiosity, the same logic can be applied if I have both positive and negative numbers? For e.g showing top 3 brands ranked by profit instead of sales amount. Thank you!

[@SQLBI]

Yes it should work.

[@ChrisStewart-mn5wm]

Wow. Incredibly useful. I have a composite model and joining to the large product table is very expensive. I'm wondering if something similar could be accomplished with a simple rank table containing only the rank numbers. Could this then "lookup" the products at that rank position? Now, might that work with fuled parameters for the dimension you want to rank/display?

[@emmanuelgq]

Thank you, it works perfectly! I had to twek the formulas a little because my project works with YTD :)

[@felipelopes7353]

Hi Alberto, thank you so much !! Is there a way to do this ranking process without the creation of a new table ?

[@SQLBI]

See this article-video: www.sqlbi.com/articles/filtering-the-top-products-alongside-the-other-products-in-power-bi/

[@davidturbucz5776]

A very useful and understandable topic! Thank you! Time to start the book!! :)

[@azadabulkalam2998]

Simply Love it sir. Excellent job.

[@marcelaandreacastrillonbui7951]

Thanks, I love the video, I followed but with percentages, and I get the others' category as 0 because the first top N sum is equal to 100% :'( do you have any idea

[@Aprilian_EU]

Thank you very much Alberto. Very good as always

[@patrickgerson6342]

Thank you very much for this great tutorial and all the advices given. Unfortunately when applying this method to my Dataset I ran into an issue when the number of products in a given category is less than the TopNValue. So for example a Category has just 1 product the maximum value for the TopNValue is 2 (TopNValue = 1 just shows the product itself, TopNValue = 2 shows the product itself and the other value with Sales Amt = 0), otherwise Power BI will rank all the peroducts in the Dataset under that Category with Sales Amt = blank and sost likely I receive an out of memory error. Do you have a solution to fix this? Thank you very, very much.

[@marcinkaczmarczyk4406]

Awesome video Alberto! You've done my life much easier. Cheers!

[@gerogi27]

Hi Alberto, Great content!! I managed to follow all your steps and it worked. My challenge is to do this for a parameter field. Instead of having only "product name", to have a parameter with multiple options. I applied the same logic as you did here, but it's loading extremly slow, and in the Desktop version it crashes. Can you help us with this?

[@SQLBI]

As explained in the video and article, this technique is not fast - but until a similar feature is implemented in Power BI, it's not possible to do something better. The field parameter adds further complexity (and slowness).

[@gerogi27]

​@@SQLBI Thanks for answering!

[@migmanc]

Amazing video, thanks a lot. I am learning so much in your channel.

[@sukhomoyb]

Hi Alberto, Till the video timeline 20:56, was awesome and I was about to become your fan. But after that, you messed up the whole. Sorry to say here, Gap No.1: you could have made the Ranking of "Others" = Rank + 1 dynamically. Gap No.2: Easily you could have calculated Others Value = Group Total - (Sum of Ranked Product's Value) Anyway, Good initiative, and I must appreciate your communication and teaching skills.

[@ballajaja]

Thank you for the detailed explanation. This helped a lot!

[@stephenbrincat7124]

Thanks Alberto, as always very well explained Ideally would have the year as a column in the matrix, so one could compare the rankings over the years 🤔🤔

[@kenzajniyah3514]

Hi Alberto, Thanks for the video. Actually, is it possible we can do it for Gross Margin % or any % ? Thanks in advance :) !

[@erikaivanauskiene5284]

Thank you very much for this great tutorial!

[@Himanshu_Yaduvanshi04]

Marco - The above solution is working for me if I am using the aggregate functions on that column. If I opted "Don't Summarize" in that case I am getting rank 1. Please let me know what I need to change.

[@georgianamocanu8560]

Hello! I have tested this and it’s really great. However I have tried to use another type of visual: bar chart/column chart and it seems that it doesn’t work as expected. Is this approach working only for matrix/table or am I missing something? Thank you!😊

[@dayhi011]

Thank you for this video. It has been very helpful!

[@hlambert100]

Thank you very much, good, good, ideas and resolution is perfect.

[@jaimemejia4387]

Awesome, it definite takes reports to the next level. It´s difficult but it worths.

[@zeisslaimen1]

as always, I have to watch 2 times. Love u, Alberto :)

[@damiendent7276]

Hi Alberto, really good video works great when you are using SUM for most columns.. However struggling to get correct final figures for Other when trying to use Average or percentages in columns.. Any help would be appreciated.

[@SQLBI]

Check the related article and look for the Sales Amt Non Additive measure: www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/

[@ИванКовачев-р5з]

Hi guys! Can i ask one question? What is the move when u want this top N to work with another visualisation ? Thanks !

[@eric_vieira]

Eu sempre fico maravilhado com podemos voar apenas com alguns minutos de conhecimento de qualidade! Como sempre digo: "Aprenda algo novo todo dia". Esse foi meu novo de hoje. VAleu!!!! #powerbinareal

[@Tom-kp2lv]

This is great! But what if, instead of subcategory, i need to use a different dim, say, salesperson?

[@barttrudeau9237]

that was amazing and inspiring! I'll be watching again and practicing

[@Hdcqt751]

He is a master, but this difficulty level for creating just Others shows how MS Power BI has a profound problem. Tableau solves this dynamic issue with a click!

[@SQLBI]

Yes, this is a workaround until Power BI implements a specific feature in the UI!

[@2404Pepe]

VERY USEFUL!!! Thanks Alberto!!

[@gh86886]

Hi Alberto. I just found if you only select 'Others' in Filters on this visual panel, the Sales Amt of Others became the same as the total of all products. How to fix it?

[@mshparber]

How can I see the details of "Others"? If I click on "Others" I would like to show the detailed products in a different table. What measure do I need to build so it shows me "Others" products?

[@pufick925]

Cool. I've done my project by your video :D Thanks a lot

[@mohanravi3532]

Hi Alberto! In a scenario, a product and category both are in same table. And I want to filter product by each category separately. i.e. products split into two slicers named bikes products and components products. When I filter each product, it should add to crosstab. Can you please help me how to achieve this?

[@AmbreCazaux]

Morning - is it possible to get number 2 (top) and number 3 (top) values only without Summing them up? ex: 1: paris (3,000£), London (£2,000) & Istanbul (£1,000). Thanks

[@kennethsimon1902]

Excellent tutorial. Really helped me a lot

[@CevicheChessSalsa]

Very nice demo. Thanks very much Alberto Ferrari !! Would you please share the pbix for learning purposes since it is not available to download in sqlbi.com ?

[@SQLBI]

Thanks for reporting it - the download is available now in the article!

[@CevicheChessSalsa]

@@SQLBI Very much appreciated Alberto Ferrari. Cheers !!