NOTE - The Pseudo Prod Table is linked to the Product Table >> Product Name Column (One to Many Relationship). DOWNLOAD PBIX - chandeepchhabra-my.sharepoint.com/:u:/g/personal/chandeepchhabra_chandeepchhabra_onmicrosoft_com/EdyCVJETeWNLhnAqqHJT1gcBN4Z7XZnrM2Bz5_wKC4jDpQ?e=f73ylA Also use this code to get the correct totals Top N SUM Table = VAR TOPNSelected = SELECTEDVALUE('TopN Selection'[Value]) VAR CurrentProd = SELECTEDVALUE('Pseudo Prod Table'[Product]) VAR TopProducts = TOPN( TopNSelected, ALLSELECTED('Pseudo Prod Table'[Product]), [Total Sales] ) VAR TopProdSales = CALCULATE( [Total Sales], KEEPFILTERS( TopProducts ) ) RETURN IF( HASONEVALUE('Pseudo Prod Table'[Product]), SWITCH( TRUE(), CurrentProd "Others", TopProdSales, CurrentProd = "Others", CALCULATE( [Total Sales], ALLSELECTED('Pseudo Prod Table'[Product]) ) - CALCULATE( [Total Sales], TopProducts ) ), [Total Sales] )

Great video. The SQLBI video was excellent but this makes the concepts even easier to understand. Thank you Chandeep! Your videos are very well done.

This is the excellent example, step by step commentary provided. This weekend, I have spent my time to get solution on the same requirement and I would say this is the best video out of all the available on the KZfaq. Great stuff Chandeep! Thank you and best of luck!

My brother, I just had a major breakthrough while watching this video for the second time. Using the instructions you graciously provided, I may have just came up with a design for my companies sales team that will prove to have major benefits across the entire department. Thank you so very much for explaining this concept in an easy to understand way. I love your tone and cadence. So much information is so easily taken in. You are awesome.

This is an excellent learning resource, thank you so much for sharing and the step by step instructions

🙌🙌 thanks a lot sir, I seen some tutorials but no one explained this much easy and clear way. Tq😍

Excellent step by step tutorial and a great business case 👍. Thank you.

Amazing video and walk-through a complex calculation and visualization. Thank you

@goodlychandeep you're a godsend. I'm prepping for an interview, needing to know this very thing. You have an excellent method of teaching and I am learning a lot. I knew how to do this on excel and SQL but not PowerBI

This is an excellent video and perfect illustration. Step by step with no errors. I mean excellent and what I was looking for. Goodly, you have yourself a customer. I will be buying your DAX course, by the grace of God this week.....

this is the best video on internet last night i haven't slept due to this problem and now i understand thanku so much !!!!

This guy make it easier to understand. Great explanation.

Excellent and very easy way , thank you for video

very very well explained, So many topics covered. Well Done Goodly.

Very useful and well explained tutorial. Thanks for that!

Excellent, Looking forward to more Videos. The way you explain is very catchy and easy to understand... Many Thanks...

really Excellent tutorial! very well explained and really helpful especially if you want build a Pie chart! Many thanks!

Thanks so much! This video let me see hope in a situation I'm trying to tackle. In addition to put the non-selected items to Others group, I already have an "Others" item in the product list(in my case it’s a brand list) and I need this Others to always be pushed to as part of the bigger Others. Also, I need to create an extra item, which is the sum of certain two brands, and also show its position alongside with the two of its components. For the bigger Others and this summed item, both their rank need to be blank. But for display order, the bigger Others needs to be place at the end while the summed item appears in accordance with its position. And the dynamic interactions with the slicers also needed. I sort of done this (for a fixed Top N number) through power query by separately making the components I need (the bigger Others, the Top Ns, the summed brands, and the Grand total, and then append them together as the final result. But I’m thinking if this is even possible in Power BI? Would be so grateful for any suggestions on the thinking direction!

Yo, this number formatting is wicked. I have never expected to see it in real life.

So nice to see you in the corner of the video!

You are a legend! I've been stuck with this for days and you solved my problem! Thank you a lot!!!!

Thanks Chandeep! This solution works well. I have tried to adapt it to calculate top performers for the next level of granularity. I have an Agent Table in my model. Some Individual agents may be part of different networks (next level of granularity). I produced measures to calculate top performing networks. When doing so, the agents who do not belong to any network were added to "Others". I am struggling with the ranking measure though, to have the "others" bar at the bottom. I would be very grateful for your help with this!

Great Video! Thanks for sharing Greetings from Medellin-Colombia

Excellent explanation. Thanks!

Hi! Many thanks for this video! I love the way you explain all aspects. 😊 Would it be possible for you to show how to set up the formula to get correct totals if we want to show tabular view? Right now the total is the sum of top N 😢

You are amazing sir,, I do not bore with your all videos. I want training for DAX.

Many thanks - really perfect!

Thank you for THIS, great and excellent learning

Agradeço pela excelente aula. Muito prático o entendimento, ajudou bastante !!!!

Thanks Chandeep. Lots to learn here. Thanks for walking through it step by step. Thumbs up!!

Amazing use of DAX. Thanks for sharing such great techniques.

Simply, thank you!

Thanks for sharing these tricks ...

wonderfully explained, thanks.

Thank you brother, I'm from Brazil and your video helped me a lot to solve important issues in my work, thank you for sharing, a big hug from Brazil!!

Super informative Thankyou so much 🤝

This is really solved my biggest problem Thank you so much!!

In your data model, join the pseudo table to the main table that it was built from. The calculation/measure to sum or count will use that main table. Once that join is there, from the pseudo table to the main table (1:M) then you should be good. If not, look for errors. For example, it didn't work at first for me because I defined by 'Other' category as 'Other' instead of 'Others'. When it came to the Top N measure I used what I saw which was 'Others'. Oops. When I changed that to 'Other' then it worked. Nice job by the creator but also nice feedback from the community to fill in the gaps.

Wonderful. Thank you very much!!

Friend! You are a genius!

Superb video Chandeep.

Loved it!

Incredible, man!!

Excellent Session.

Spell bound. How a complex problem solved with ease. And , the way you explained each step is super. Keep enriching us with your great ideas. How can we get the top product of the current year and show its trend over the years?

Thank You, Very Well Explained

Awesome video! Hats off to you for explaining the part very neatly and easily. Keep posting such videos.

Excellent 👍

This is a great guide and clear however is there a way to add additional slicers from other tables that will still show the "Others" on the chart?

Great one

Great video!

Great Video

GREAT JOP MANY THANKS

Thank you Chandeep it was a great video. I have a requirement like one table and one slicer slicer values like A, B, C if I click a then display b+c values and if I click b then a+c values like that...

After a few weeks, I had to watch this video again, and finding - 21:14 - saved me a lot of time

Awesome!!

Great Video and thank you for sharing this with us. I have a question, is there a way display the top 3 product as stacked column char ?

Thanks a lot your work is great. can you share one video for chart formatting and full report formatting. if possible please share all element formatting video.

Man, you're amazing!

Thank you! This was very insightful and useful!

Amazing video. really useful and intellectual idea

Thanks for the video. Can you please advice , I am able to get the Clustered chart for top 5products+Others but I need a Stacked Chart with the split of each bar for Top 5 Products eg:Top1,Top2.... to be splinted on the basis of Model Name according to the Dataset you have provided.

Thanks, great help! How can we show the grand total to include the "others" in its sum?

One Suggestion! You can also use what-if parameter for topn value selection. In that case you wouldn't require a separate table for slicer!!

Amazing content

Awesome this was one of interview questions for me I got the answer

Really a great video with clear explanation.

HI, Excellent tutorial, thank you. What if I needed to find out how many times a product appeared in TOPN, for example in 2004? Let's say I evaluate TOPN on a weekly basis. I can't create a virtual table and then view the result. The visual obviously has a problem with the summarize and summarize columns functions. Is there a solution?

you save lives !

Thanks Chandeep for this amazing video. (Already seen Sqlbi masters alberto)..but you have great explanation 👍. Could you show how this pseudo table is connected with sales table?

Thank you so much for this.. Its really helps a lot.. Can we also add subtotal in the graph.. Please reply

Superb

the way you explained was awesome , keep up the good work

Excellent content

Super Helpful Video -- How would adjust this to rollup correctly in a matrix? Currently, I am getting this to show only values of the top values and not the "others"

Very useful. Thank you

Thank you very much! You are amazing!

Excellent explanation, one question if the others value is higher than the TopN how can I place in bottom of the list? Thanks

Great Video!! Could you make a video on DAX function openingbalanceMonth, Quarter & Year

Great Video . Have you joined pseudo product table with sales table?

thanks for the video but its super tricky

Nicely explained.

Thank you, I really appreciate your help...it's really great.... :)

18:14 Thanks a million. But do you know if there is a way to also calculate the totals correctly?

thanks a lot. you made my day

How would you tackle this when the dataset includes multiple sale measures. For instance, actual sales and budgeted/forecasted sales?

Hi Sir - Thank you very much for this amazing video. One question. How do I let power BI recognize the list of categories and other information (like in my case i have customer, contract per customer and hours by employees) from that OTHER list combined? every time I click others on my chart, the total cards will show only blank.

SWEEEEEEEEET!!! Thanks for that!!!!

you are a beast!!

Nice tutorial! However, how did you add the title variable in the text visual?

HI, Thanks for this. Can we make the category dynamic by field parameter instead of product, we can choose Shade, Category, Customer, State etc on the report like top n days?

congratulations for the video. Doubt: if I click on 'others' will it cross filter?

Thank you Chandeep - this is teh type of the analysis that makes analysis excellent. We analysts need to make it easier for the consumers to make it easier to digest the huge amounts of data. Thank you Sir

Thank you very much, very good solution however I have a problem. When selecting a top, 2 for example, if the two dimensions cover 100%, the "Others" bar will still appear, but with a value of 0. Can it be prevented from appearing? It also appears at No. 1 according to the order of the ranking.

Great Video, similar to this video, can you make a video on top n plus others PERCENTAGE wise.

You are god of Power BI

You are a legend :)

Hi Chandeep, hope you're doing well.. How can I create stacked bar chart considering only top5 products and countries as legend and on X-axis? I am only able to select top products using topN...

Hi! Thanks for the amazing tutorial. It is really very well explained. However, I am stuck at creating an area chart with TopN + Others and using it in the legend, can you please help me out here?

Awesome video but I'm stuck on one point, your Total Sales parameter. Can you explain how you built the parameter?