Power BI: Show unselected values as Others
Problem Statement: In a slicer, we have a set of values. If a value is selected, then show the unselected values as others.
Solution Approach: There are two ways in which we can solve this problem: by adding additional dimensions or by using extended dimensions. In this case, we are going to discuss how to use an extended dimension.
The following is an example of how the test data and the dimension data look like
The data model view for a better understanding of the data
Here is what the current view looks like when no value has been selected
When you select a value, the visual only displays it. Unselected values are not displayed in the visual when you select a value.
There is a requirement that unselected values be displayed as a new value labeled as others.
We are planning to create a new table that has the same values as the current dim table but also has the additional value “Others”, which is an extended dimension that will be joining the dim table in the model.
As a result, the model will now look like this:
There is now a need to create a measure that will ensure that “Others” has a sum of the values of the unselected values
The DAX code for the measure that needs to be calculated
Value + Other2 =
var _isfilter = CALCULATE(ISFILTERED(Dim[Key]), ALLSELECTED())
return
if(_isfilter,
if(max('Key'[Key]) ="Others" , CALCULATE(sum(Data[Value]), all(Data)) - CALCULATE(sum(Data[Value]), ALLSELECTED(Data)), sum(Data[Value])),
sum(Data[Value]))In order to demonstrate the functionality of the new measure, I created two visuals. The first one does not interact with the slicer. The second one interacts and makes use of the newly introduced measure.
As you can see in the second visual, the selected value “A” still contributes 40% of the total, with the rest taken by “Others”.
This file can be found here if you would like to download it.
There is a video that shows you how to do this step-by-step.
My Medium blogs can be found here if you are interested
Click here to access all my blogs and videos in a jiffy via an exclusive glossary using Power BI. Please like, share, and comment on these blogs. I would appreciate your suggestions for improvement, challenges, and suggestions for topics so that I can explore these in more depth.
There is a Power BI tutorial series that you can watch on my channel. Make sure you subscribe, like, and share it with your friends.
Master Power BI
Expertise in Power BI
Power BI For Tableau User
DAX for SQL Users
