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] )
Is it possible to create a drill through from this chart for 'Others' to a detail page where it shows details of data that belongs to this 'Others' category.? Thanks.
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 TH-cam. Great stuff Chandeep! Thank you and best of luck!
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.
I have linked my pseudo table to the main table but I still cannot get the topN function to get the total sales figure to show up. What am I doing wrong?
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.
@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.....
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 😢
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!
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?
Its not one product left. Its when each row acts as a filter when we drag TOP N sum then it means that there is one value that it has. So one value would always be among the top 3 or bottom three or any three or max three anything . Hence it is giving the sales amount of that particular value in product column.
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.
Really appreciate this video. Sadly I fell down at the 15:45 mark because the Others Category does not appear for me if I try and filter the data using another connected table.
2 ปีที่แล้ว
Agradeço pela excelente aula. Muito prático o entendimento, ajudou bastante !!!!
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?
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?
thanks for the great video :) I am trying to replicate this for bottom N sales (with a rankx function), but cannot fix the "Others" - it sums the total amount of sales and does not subtract the selected bottom products. Is there a way to fix that?
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
Hi, thank you so much for this video!! I followed all your steps and everything adds up perfectly. I also added the relationship, but the sum of my Grand total is incorrect, it give me the total of TopProdSales . Could someone please advice.. 😊
For someone that might have this issue in the future.. Here is what worked for me. Replace the Return statement with this: RETURN IF( HASONEFILTER('Pseudo Prod Table'[Product]), IF( CurrentProd "Others", TopProdSales, OtherSales ), TopProdSales + OtherSales )
This is a great video and was very helpful to me. However I think the video misses out the need to connect the newly created table of products with the extra "Others" line item added, to the existing original table of products, using a one ( from the new pseudo table to many relationship relationship ( on the original table). Otherwise the Top N calculations simply don't work as your employing measures on a fact table that need to be filtered by a dimension coming via the original products table. I employed your technique to create an "Others" total for a sales by Customers table, my use case was a little more extreme as I had multiple business units I needed to filter down as well on the Topn dimension , plus I was using switch formulas to move between different Metric Qtys, ( ie Sales $ and Sales Kg and Avg Prices) rather than working on a products table but the nonetheless same ideas held. However I could not get the technique to work until I had connected the new "psuedo" table to the original table using a one to many relationship. This doesn't appear to be addressed in the video and the video leaves the impression after creating the new psuedo table you can dive straight in and create new measures employing the psuedo table, and you can but they simply wont work until you connect up the tables.
No worries mate, I am subscribed to your channel now. Thanks again for a great video, happy I could help out a little and pick up a minor issue. Shows me my learnings of dax are starting to improve :)
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.
When i use this dax function iam facing an issue. When two or more values are same it is considering those values as one and showing all values. What i want is only it shoud show top 5 records even values are same. How to achive it
How can I create top n and others in heirarchy For ex Country top n City top n Region top n Ranged by variance And other values in this metrics are security price ,market value
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?
Hi Chandeep ... I am having trouble replicating this .. I keep getting an error statting that A table of multiple values was supplied where a single value was expected .. would you be able to help me troubleshoot this?
Hi! Thank it did work but I have a question, what if in the top 3 I want to exclude 2 of the products that are there, but in the sum of "Others" I do want them in there?
Hi, When I use color change measure in conditional formatting as explained in the video it does not work. It reflects mdx Script error (45,3). How to resolve the error.
Hi! 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?
This is not working for me, for some reason power BI in the "others" category only sums ALL the sales, without subtracting the first top 3, I have trying modifying the formula to no avail
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.
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]
)
Is it possible to create a drill through from this chart for 'Others' to a detail page where it shows details of data that belongs to this 'Others' category.?
Thanks.
Is it possible to create "Others" In a live Connection?
@@jishnusachidanandan5866 any solution for this?
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 TH-cam. Great stuff Chandeep! Thank you and best of luck!
Legal
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.
I have linked my pseudo table to the main table but I still cannot get the topN function to get the total sales figure to show up. What am I doing wrong?
Legal
Yep, without joining the pseudo table the Top N measure won't work.
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.
I am glad it workout well Joel!
Yo, this number formatting is wicked. I have never expected to see it in real life.
It's always so cool to find EXACTLY what we need. Thank you sir!
this is the best video on internet last night i haven't slept due to this problem and now i understand thanku so much !!!!
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!!
hugging you back :)
@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.....
Thank you. Looking forward to seeing you in the course.
This guy make it easier to understand. Great explanation.
🙌🙌 thanks a lot sir,
I seen some tutorials but no one explained this much easy and clear way. Tq😍
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 😢
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!
You are a legend! I've been stuck with this for days and you solved my problem! Thank you a lot!!!!
Glad it helped!
Excellent and very easy way , thank you for video
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?
Awesome video but I'm stuck on one point, your Total Sales parameter. Can you explain how you built the parameter?
You are amazing sir,, I do not bore with your all videos. I want training for DAX.
This is really solved my biggest problem Thank you so much!!
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?
Amazing use of DAX. Thanks for sharing such great techniques.
Glad you like it! 💚
Thanks Chandeep. Lots to learn here. Thanks for walking through it step by step. Thumbs up!!
Glad it was helpful!
Its not one product left. Its when each row acts as a filter when we drag TOP N sum then it means that there is one value that it has. So one value would always be among the top 3 or bottom three or any three or max three anything . Hence it is giving the sales amount of that particular value in product column.
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.
This is an excellent learning resource, thank you so much for sharing and the step by step instructions
Excellent, Looking forward to more Videos. The way you explain is very catchy and easy to understand... Many Thanks...
Amazing video and walk-through a complex calculation and visualization. Thank you
very very well explained, So many topics covered. Well Done Goodly.
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!!
So nice to see you in the corner of the video!
Awesome video! Hats off to you for explaining the part very neatly and easily. Keep posting such videos.
Glad you liked it!
18:14 Thanks a million. But do you know if there is a way to also calculate the totals correctly?
Have you managed to find the solution? 😢
@@annaokarmus1334 nope. Not yet
@@andrestricker4118 thanks :( struggling with the same!
Thank you for this video. However, by selecting "Others" on the visual, it returns a blank value in a card visual. Is this expected?
Really appreciate this video. Sadly I fell down at the 15:45 mark because the Others Category does not appear for me if I try and filter the data using another connected table.
Agradeço pela excelente aula. Muito prático o entendimento, ajudou bastante !!!!
Que bom que ajudou!
Very useful and well explained tutorial. Thanks for that!
Great video! Is there anyway to drill down on “Others ” to see the details?
Awesome this was one of interview questions for me I got the answer
Friend! You are a genius!
How do you add other measures such as profit as another column in such a chart? How would you go about creating that measure?
Great Video!
Thanks for sharing
Greetings from Medellin-Colombia
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?
really Excellent tutorial! very well explained and really helpful especially if you want build a Pie chart! Many thanks!
Thank you for THIS, great and excellent learning
Thank you! This was very insightful and useful!
You're so welcome Andreea!
Great Video, similar to this video, can you make a video on top n plus others PERCENTAGE wise.
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?
Hi, did u find a solution to this?
Excellent explanation. Thanks!
thanks for the video but its super tricky
Really a great video with clear explanation.
Thank you!
wonderfully explained, thanks.
Simply, thank you!
You are the best! Thank you
Thank You, Very Well Explained
thanks for the great video :) I am trying to replicate this for bottom N sales (with a rankx function), but cannot fix the "Others" - it sums the total amount of sales and does not subtract the selected bottom products. Is there a way to fix that?
Great Video!! Could you make a video on DAX function openingbalanceMonth, Quarter & Year
And what have you calculated in Top N SUM measure?
Why do we need IF top n sum Blank(). ???
did you ever found out?
How can I modify the code to always have the "Others" row at last?
Super informative Thankyou so much 🤝
Amazing video. really useful and intellectual idea
Glad you like this Vishnu!
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
Excellent content
Glad you think so Krishna!
You are god of Power BI
How would you tackle this when the dataset includes multiple sale measures. For instance, actual sales and budgeted/forecasted sales?
Could you please tell how do we use this method with a legend in our column chart and keep these top N fixed?
Nicely explained.
Thanks Suresh!
Hi, thank you so much for this video!!
I followed all your steps and everything adds up perfectly. I also added the relationship, but the sum of my Grand total is incorrect, it give me the total of TopProdSales . Could someone please advice.. 😊
For someone that might have this issue in the future.. Here is what worked for me.
Replace the Return statement with this:
RETURN
IF(
HASONEFILTER('Pseudo Prod Table'[Product]),
IF(
CurrentProd "Others",
TopProdSales,
OtherSales
),
TopProdSales + OtherSales
)
My dear - really struggling to resolve RANKX function issue. Any help will be highly appreciable. Happy to share my code and scenario.
Thanks for sharing these tricks ...
Wonderful. Thank you very much!!
Excellent Session.
This is a great video and was very helpful to me. However I think the video misses out the need to connect the newly created table of products with the extra "Others" line item added, to the existing original table of products, using a one ( from the new pseudo table to many relationship relationship ( on the original table). Otherwise the Top N calculations simply don't work as your employing measures on a fact table that need to be filtered by a dimension coming via the original products table. I employed your technique to create an "Others" total for a sales by Customers table, my use case was a little more extreme as I had multiple business units I needed to filter down as well on the Topn dimension , plus I was using switch formulas to move between different Metric Qtys, ( ie Sales $ and Sales Kg and Avg Prices) rather than working on a products table but the nonetheless same ideas held.
However I could not get the technique to work until I had connected the new "psuedo" table to the original table using a one to many relationship. This doesn't appear to be addressed in the video and the video leaves the impression after creating the new psuedo table you can dive straight in and create new measures employing the psuedo table, and you can but they simply wont work until you connect up the tables.
Thanks Paul. I missed mentioning that critical part in the video. My bad!
No worries mate, I am subscribed to your channel now. Thanks again for a great video, happy I could help out a little and pick up a minor issue. Shows me my learnings of dax are starting to improve :)
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.
When i use this dax function iam facing an issue. When two or more values are same it is considering those values as one and showing all values. What i want is only it shoud show top 5 records even values are same. How to achive it
you save lives !
Many thanks - really perfect!
How can I create top n and others in heirarchy
For ex
Country top n
City top n
Region top n
Ranged by variance
And other values in this metrics are security price ,market value
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?
can someone explain at 8.00 whats going on after we put our measure TOP N sum sales why we get the same result as total sales??
congratulations for the video.
Doubt: if I click on 'others' will it cross filter?
I am sad that this functionality still isnt native to Power BI after all these years
Thank you so much for this.. Its really helps a lot.. Can we also add subtotal in the graph.. Please reply
Can we show top 5 in one visual and also show others which are not in top 5 in another visual? Please let me know how
Hi Chandeep ... I am having trouble replicating this .. I keep getting an error statting that A table of multiple values was supplied where a single value was expected .. would you be able to help me troubleshoot this?
Hi! Thank it did work but I have a question, what if in the top 3 I want to exclude 2 of the products that are there, but in the sum of "Others" I do want them in there?
Can someone help me out sorry :( what does the breakdown of [Total Sales] look like? It seems to be another measure.
Excellent presentation but Could you please help me something? How i could bring more fields on the pseudo table (like descriptions or other info)
Excellent explanation, one question if the others value is higher than the TopN how can I place in bottom of the list?
Thanks
Great one
Superb video Chandeep.
Hi, When I use color change measure in conditional formatting as explained in the video it does not work. It reflects mdx Script error (45,3). How to resolve the error.
Hi! 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?
GREAT JOP MANY THANKS
When i am filtering Others while using score cards it showing blank can you help me in that.
This is not working for me, for some reason power BI in the "others" category only sums ALL the sales, without subtracting the first top 3, I have trying modifying the formula to no avail
I tried I it shows a mistake
Measure 4 =
VAR TOPPRODUCTTABLE=
TOPN(5,
ALLSELECTED('Name Alias'),
[Sale]
)
VAR TOPPRODUCTSALE =
CALCULATE(
[Sale],
KEEPFILTERS(TOPPRODUCTTABLE)
)
VAR OTHERPRODUCTSALE =
CALCULATE(
[Sale],
ALLSELECTED('Name Alias')
)-
CALCULATE([Sale],
TOPPRODUCTTABLE
)
VAR CURRENTPRODUCT = SELECTEDVALUE('Name Alias'[ITEMNAME])
RETURN
IF(
CURRENTPRODUCT "OTHERS",
TOPPRODUCTSALE,
OTHERPRODUCTSALE
)
can you check details
Incredible, man!!
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.