Show TopN Products and Others in Power BI

- - - - My Courses - - - -
✔️ Mastering DAX in Power BI -

✔️ Power Query Course-

✔️ Master Excel Step by Step-

✔️ Business Intelligence Dashboards-

- - - Blog & Videos - - -

[GoodlyChandeep]

NOTE - The Pseudo Prod Table is linked to the Product Table >> Product Name Column (One to Many Relationship).


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

[JohnSullivan]

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.

[tukaramjogdand]

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 YouTube. Great stuff Chandeep! Thank you and best of luck!

[aspitball]

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.

[joelabacherli]

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.

[seazonegranec]

It's always so cool to find EXACTLY what we need. Thank you sir!

[TomaszBI]

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

[annaokarmus]

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 😢

[datayoda]

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

[kunalkumar-hlgv]

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

[Ravi-ceth]

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

[juliotorres]

@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

[haleyalltheway]

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!

[feliperodrigues]

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!!

[KawanExcel]

This guy make it easier to understand. Great explanation.

[ramakrishnaravula]

Excellent and very easy way, thank you for video

[andrestricker]

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

[robertduncan]

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?

[joserobertodantas]

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

[johncoker]

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