Comparing Selected Categories in Power BI

How to compare multiple selected categories in Power BI without duplicating tables, slicers or visuals

Photo by Austin Distel on Unsplash

Introduction

For conducting proper reporting, it’s often required to dynamically compare one or more categories against each other based on the current filter context. The easiest way to achieve this in Power BI is to simply use the slicer visual together with any other visualization type providing a category/column field where you can drag in the relevant information. Another way to achieve such a comparison even across visualizations is to duplicate all the required visualizations including slicers and change how they interact with each other. For instance, you can have two slicers whereas each of them only filters a certain set of visualizations in your report page. You can also use one and the same visualization providing two slicers without having overlapping items, see here.

Requirement

However, what if you want to know, let’s say, the difference between the current selections and to have each of those categories dynamically as a separate measure without duplicating any objects, to achieve something like this:

Image by Author

Solution

This just requires you to create a few Measures, but nothing really complex, let’s see what we need for this.

First, we need to capture the current selection of the desired field used within the slicer visualization. What the next Measure does is, it first checks if not more than two items are selected and then simply takes the selected values and separates it with a vertical bar so that we can later easily split it by using the PATHITEM function of DAX.

Selection:

Selection =
VAR _Count = COUNTROWS(VALUES(Executive[Name]))
VAR _Concat = CONCATENATEX(VALUES(Executive[Name]),[Name],”|”)
RETURN IF(_Count>2,”Please select only 2",_Concat)

Selection1:

Selection1 = IF((PATHITEM( [Selection], 1 ) = “Please select only 2”),”Please select 1",PATHITEM( [Selection], 1 ))

Selection2:

Selection2 = IF((PATHITEM( [Selection], 2 ) = “”),”Please select 2",PATHITEM( [Selection], 2 ))

Once we have captured the selected values by using above Measures, we can use their returned values in another Measure’s filter context which we then, in turn, can use to make comparisons against each other.

Selection1Measure:

Selection1Measure =
VAR sel = [Selection1]
RETURN CALCULATE([Total Revenue],Executive[Name] = sel)

Selection2Measure:

Selection2Measure =
VAR sel = [Selection2]
RETURN CALCULATE([Total Revenue],Executive[Name] = sel)

SelectionVariance:

SelectionVariance = [Selection1Measure]-[Selection2Measure]

You final set of Measures should look something like this:

Image by Author

Conclusion

This is a very easy and dynamic way to achieve comparisons between the currently selected items without needing to duplicate any objects. You can define as many selections as you like including different kind of measures you want to see in your comparison. If you are not sure whether you have everything used correctly or just to speed things up, you can download an example report from my Github account which is based on an example provided by Microsoft.