The above video walks you through how to set up a stacked bar chart to show 1 (or more) categories against the rest of the categories in Power BI with DAX. This is a bit difficult to explain without an example, so here’s a gif showing the finished product:

Here’s the final DAX code necessary to set this up:
Sales vs Others = VAR CurrentCategory = MAX('Categories + Other'[Category Name]) VAR CurrentCategoryStacked = MAX('Categories + Other (Stacked)'[Category Name]) VAR SelectedCategories = ALLSELECTED(Categories[Category Name]) VAR PossibleCategories = ALL(Categories[Category Name]) RETURN SWITCH( TRUE(), CurrentCategory IN SelectedCategories && CurrentCategoryStacked = CurrentCategory, CALCULATE( [Total Sales], Categories[Category Name] = CurrentCategory ), CurrentCategory = "Others", CALCULATE( [Total Sales], FILTER( ALL(Categories[Category Name]), Categories[Category Name] IN PossibleCategories && NOT(Categories[Category Name] IN SelectedCategories) && Categories[Category Name] = CurrentCategoryStacked ) ) )
I hope you enjoyed learning this advanced trick! If you would like to access the data behind this report, make sure to check out my Training courses here. All memberships grant you access to a live SQL Server database to practice Power BI!
Parker Stevens
Parker is the creator of the BI Elite YouTube channel, a community of over 30,000 students learning Power BI, DAX, and Power Query. He is a Microsoft Data Platform MVP for his work with Power BI and continues to provide elite Power Platform training courses to help data analysts, BI developers, and citizen dashboard creators reach their goals. Parker is committed to producing high-quality training content that is also extremely cost-effective, to ensure that the largest amount of users can benefit from the content. Training courses located at https://training.bielite.com/
4 Comments
Leave a ReplyCancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Thanks! This was helpful however how would you compare one category to multiple categories and the remaining to others. Basically, users can compare category A to category B, C and categories D and E will fall under other? Thanks in advance!
Great video, I’ve replicated to my use case however how is the tooltip incorporated? It doesn’t show a count individually for Category A and the Others.
I have tried to then create a Tooltip report page to try get around this, but how do you join this table that includes Others along with the original categories to join to a data model to then auto filter when hovering over a particular bar etc
How did you measure [Total Sales9] ?
Thanks – great idea really well presented!
It looks like the code provided doesn’t match the code in the video. The variable ‘CurrentCategoryStacked’ isn’t in the video. Also for consideration: using DISTINCT in place of VALUES to generate the table will ensure the new table doesn’t contain blank rows.