Have you ever wanted to compare metrics between 2 sets of selected values in the same dimension in a Power BI report? For example, let’s say you wanted to compare claims metrics of one hospital or a group of hospitals to another hospital or group of hospitals, with the ability to slice on which hospitals to use on each side of the comparison vs doing some type of segmentation based upon metrics or creating additional hierarchies or groupings in the dataset. In this article I will outline a technique for doing so which overcomes the inherent filter context of a traditional star schema as well as not requiring dataset changes whenever you want to group by different dimension values. To illustrate this solution, I used the AdventureWorksDW Database as the data source. I will first take you through creating the DAX calculations and tables needed so end user can compare a single measure, Reseller Sales Amount, between different Sale Region groups. We will later extend the solution to support additional measures between different Sales Regions.
Below are the steps to compare the measure Reseller Sales Amount between different Sales Regions sets. We need 2 copies of the table containing Sales Region and 2 measures to return the Reseller Sales Amount for each Sales Region filter.
Selected Sales Region 1 = CALCULATE (
[Reseller Sales Amount],
TREATAS (
VALUES ( 'Sales Territory Filter 1'[Sales Region] ),
'Sales Territory'[Sales Region]
)
)
Selected Sales Region 2 = CALCULATE (
[Reseller Sales Amount],
TREATAS (
VALUES ( 'Sales Territory Filter 2'[Sales Region] ),
'Sales Territory'[Sales Region]
)
)
Var Sales Region 1 Vs 2 = [Selected Sales Region 1] - [Selected Sales Region 2]
Var% Sales Region 1 = DIVIDE([Var Sales Region 1 Vs 2],[Selected Sales Region 1])
Region 1 Selection = CONCATENATEX (
ALLSELECTED ( 'Sales Territory Filter 1'[Sales Region] ),
'Sales Territory Filter 1'[Sales Region],
", "
)
Region 2 Selection = CONCATENATEX (
ALLSELECTED ( 'Sales Territory Filter 2'[Sales Region] ),
'Sales Territory Filter 2'[Sales Region],
", "
)
Region 1 vs 2 = [Region 1 Selection] & " vs. " & [Region 2 Selection]
Reseller Sales Amount Region Label = "Reseller Sales Amount, " & [Region 1 vs 2]
Below is a Power BI report showing slicers for the 2 new disconnected Sales Region tables comparing Southeast and Southwest vs Northeast and Northwest. For testing, I included the Sales Region table with relationship to the fact table which shows that the totals for Southeast and Southwest and for Northwest and Northeast match the Selected Sales Region 1 and Selected Sales Region 2 measure totals.
If the end user is only interested in comparing 1 measure between different dimension values, the work is done! However, if they want to compare using multiple measures, you can create a “measures dimension” to filter which measure to display in your visualizations. I originally tried creating the measures dimension using a calculation group, but filtering using the disconnected region tables did not work as expected over the calculation group items. Hence, I relied on another technique of creating a table containing the names of existing measures to filter on followed by creating the DAX calculated measures to return the result of the selected measure and sales regions.
Switch Measures = {"Reseller Sales Amount","Reseller Sales Margin", "Reseller Sales PY", "Reseller Sales Margin PY","Reseller Sales Order Qty"}
Switch Value = IF (
HASONEFILTER ( 'Switch Measures'[Value] ),
SWITCH (
SELECTEDVALUE ( 'Switch Measures'[Value] ),
"Reseller Sales Amount", [Reseller Sales Amount],
"Reseller Sales Margin", [Reseller Sales Margin],
"Reseller Sales PY", [Reseller Sales PY],
"Reseller Sales Margin PY",[Reseller Sales Margin PY],
"Reseller Sales Order Qty",[Reseller Sales Order Qty]
)
)
Sales Region(s) 1 = CALCULATE (
[Switch Value],
TREATAS (
VALUES ( 'Sales Territory Filter 1'[Sales Region] ),
'Sales Territory'[Sales Region]
)
)
Sales Region(s) 2 = CALCULATE (
[Switch Value],
TREATAS (
VALUES ( 'Sales Territory Filter 2'[Sales Region] ),
'Sales Territory'[Sales Region]
)
)
Variance Sales Regions 1 vs 2 = [Sales Region(s) 1] - [Sales Region(s) 2]
Variance% Sales Region 1 = DIVIDE([Variance Sales Regions 1 vs 2],[Sales Region(s) 1])
Selected Values = CONCATENATEX (
ALLSELECTED ( 'Switch Measures'[Value] ),
'Switch Measures'[Value],
", "
)
Region 1 vs Region 2 and Measure = [Region 1 vs 2] & " by " & [Selected Values]
Report Title =
VAR SalesRegion1 =
CONVERT ( [Region 1 Selection], STRING )
VAR SalesRegion2 =
CONVERT ( [Region 2 Selection], STRING )
VAR SwitchValue =
CONVERT ( [Selected Values], STRING )
VAR DisplayLabel =
"Region 1: " & SalesRegion1 & UNICHAR(10) & "Region 2: " & SalesRegion2 & UNICHAR(10) & " Measure(s): "& SwitchValue
RETURN DisplayLabel
You can use visualizations besides slicers to filter on the measures dimension, allowing multiple measures to be displayed in the same visualization for the selected regions:
This solution could be further enhanced to handle different measures, but different dimension attributes as well.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.