Calculation Groups – variances in a pivot directed by Slicers

I want to show a situation how using Calculation Groups we can create a pivot or matrix in which using 2 different drop downs (slicers), a user would compare different versions of data in the pivot and their variances. For example, if in the slicer 1, you select Brand A and in slicer 2, you select Brand B, then in the pivot you will see Brands A and B side-by-side and then their variance.

The slicers can look something like this: they will be used to determine which measures the pivot displays and compares variances.

After the filters are created with 2 separate identical tables with Brand Names, you need to link them to your pivot calculations. To do that you need to use Calculation Groups. Open your Power BI Desktop, External Tools, Tabular Editor.

To get Tabular Editor, you can download it for free here Tabular Editor.

Create new Calculation Group in the Tables folder:

I named the Calculation Group as Brand Comparison. Created Brand A, Brand B and renamed “Name” into “Brand Variance”. But its up to you how to name the fields.

In Brand A item, you want to make sure that it is the measure you selected in slicer 1. But also make sure it doesnt get overridden by your selection in slicer 2.

SELECTEDMEASURE() is used conveniently to tell PowerBI that which ever Measure we put in the pivot (be it sales or purchases or inventory amount), it will apply same calculations to that Measure.

To do that we can write this DAX code:

For Brand B item, we can write the following DAX. Also this time we specify that both slicers should be set to selectionb (what is selected in a second slicer) in order to not reset one another:

And for Variance item, we set the code as follows. It is just a copy of Brand A and Brand B code with a variance (formula 2- formula 1) at the end:

Once we save, we should see new field in the PowerBI that we created. if for some reason, it doesnt appear, then save PowerBI file and re-open it.

Pull Comparisons column into your pivot columns and insert any of the measures in the values of the pivot and you should be all set.

Below is a PowerBI embedded where you can interactively switch between Brands and see it in action. You can also add multiple items in the slicer and have more complex calculations such as YoY %, but the principle is the same.

I wrote a separate post on how to apply different format on Calculated Items within the Group. Here is the link: Formatting Calculated Items of Calculated Groups in Tabular Editor » Data Empower

Check out my video on Calculation Groups and formatting here: Power BI: Calculated Groups and Formatting using Tabular Editor (ganjing.com)