How to show the sum of non-selected categories grouped together as "Other" category in visuals

I recently came across an interesting problem regarding how you can show the sum of values of certain selected categories as separate bars in a bar chart – but with the sum of all the non-selected categories being grouped together in an “Other” category/bar. If you take a look at the above video, you can see that as I select various categories the value for the “Other” bar is changing (the last bar). It also shows you the difference between the Total Sales and all the selected categories. If all the categories are selected (or no category is selected), then the “Other” bar disappears from the visual.

The particular business use case is that when there are too many categories, visuals can get very cluttered. I wanted to come up with a solution for this problem and I came up with this method to show only selected categories as individual bars with the rest of categories summed together in a separate – “Other” – bar in the visual. This method isn’t only applicable to bar charts – you can use it in any visual!

Let’s first look at our sample data and then the solution. The following sample data contains only three columns: Category, Sales, and Category Sort. The Category Sort column is used to sort by column for the Category column .

First and foremost part of the solution is to create a table with distinct Category values and add a record with Other as category value and this will be used to show sum of all non-selected categories, See screen shot below. I gave the sort order value for Other row to 999999, big enough number that it shows up at as the last value in the visuals, if we want Other to be the first row in the visual, I will set the Category Sort value to like negative 1 (-1).

Make sure this Category table doesn’t have relationship with your main data table.

I added following measures to make the calculation work, I created multiple measures but all this can be collapsed into one, my performance preference is to create separate measures, so that measures can be recycled where possible.

Sum Sales is a base measure.

Sum Sales = SUM ( Data[Sales] )

Sum All is a measure used to get us Total Sales regardless of selection of Categories.

Sum All = CALCULATE ( [Sum Sales], ALL ( Data[Category] ) ) 

Sum Others is a measure to show what value we have for non-selected categories, in other case, what value we will see in Other category when we will visualize our data.

Sum Others = [Sum All] - [Sum Sales]

Sum Sales and Others is the core measure which we will use in the visual, I have added the comments in the measure to explain what each row in the measure is used for.

Sum Sales and Others =
VAR __valueOther = "Other" --variable to store the value for "Other" so that we have it at one place to use in the DAX
VAR __selectedCategory =  --create a table of selected categories and append "Other" as an extra row
UNION (
    VALUES ( Data[Category] ),
    { __valueOther }
)
RETURN
IF ( MAX ( Category[Category] ) = __valueOther,  --check if current value is "Other"
    IF ( [Sum Others] > 0, [Sum Others] ), --if condition to suppress other value in case all the categories are selected,
    CALCULATE (
        [Sum Sales],
        //TREATUS is used to filter data table
        TREATAS  (
            INTERSECT ( VALUES ( Category[Category] ), __selectedCategory ),  --get list of categories that are selected
            Data[Category]
        )
    )
)

In above measure, if want to show 0 value for Other if all the categories are selected then remove IF condition line in the measure and just simply use Sum Others measure.

Since we have all our measures in places, we will visualize the data. Let’s add a slicer for Category and make sure the Category column in this case is used from your Data Table not from Category Table.

Add a Stacked Column Chart or any other visual you would like to see. On X-Axis, put Category from Category Table and put Sum Sales and Others measure in Values section.

👉 Make sure on X-Axis, put Category from Category Table, not from Data Table.

and that’s it, as you can see at the start of the post, Other bar value will change based on the selected categories. I hope you can leverage this technique to meet your business needs. Let me know what you think. Share your thoughts.