Allowing users to choose any two different metrics to compare in a single chart with calculation groups.

This is a relatively straightforward thing to achieve in a report using calculation groups but nonetheless, very useful and a good introduction to some of the concepts around working with calculation groups.

The end result is a column and line combo chart with two slicers allowing users to choose which metric to use for the columns and which to use for the line. In effect, it allows users to select two different measures to compare in one chart.

Step By Step:

Each slicer will use a calculation group with the measures for the slicers as the calculation items. The first thing I like to do for these types of calculation groups that in essence overwrite an existing measure is create what I call a “placeholder measure” for use in the visual. This keeps things clear and simple with naming and there will be issues if you use one of the measures from the calculation groups as a base measure instead of a placeholder measure.

So first we create two measures to use in the chart called “Metric 1 Measure” and “Metric 2 Measure”. Both of these can be 0 like this:

I am purposely setting up these calculation groups so they can be used for other purposes later on otherwise you could use names more clear for this purpose like “Column Metric” and Line Metric”.

We then create a calculation group called “SalesMetric1” and create a calculation item for the first measure we want in the slicer. Here I am using Sales Amount:

We then continue to create calculation items for the other measures for the slicer. Lets rename the column “Name” to “Metric”. You can set the ordinal number later if you want to sort the list differently.

Now that we have our list of measures for the first slicer, we need to restrict which measures it acts upon in the report so that it acts upon our placeholder measure “Metric 1 Measure” only. Otherwise the slicer will change every measure present in the report. Once we create a second slicer, one slicer would change the other depending on the calculation groups precedence which dictates the order in which the calculation groups act upon a measure ( the higher the precedence, the higher the calculation group is in the order multiple calculation groups are applied in ).

To restrict the first calculation group to act only upon “Metric 1 Measure” we add some conditional logic to each calculation item using the ISSELECTEDMEASURE function which returns true if the measure referenced is being evaluated (you can also reference multiple measures ). So we add this to each calculation item as follows to make the calculation items only affect “Metric 1 Measure”.

Each calculation item is altered with the same conditional logic using ISSELECTEDMEASURE()

Conditional logic addedto the Sales Amount Calculation Item

The same logic added to the Margin % Calculation Item

The logic is as follows: If the measure being evaluated is [Metric 1 Measure] then apply this measure otherwise return the measure being evaluated. It’s important to specify SELECTEDMEASURE() as the alternate result as the default behaviour of the IF function is to return BLANK() if no alternate value is specified. In this application this has the effect of blanking the other calculation groups calculation items once both are active in the report.

Something else that can trip you up is that if we now created our second calculation group for the second slicer, the format string of one calculation group can overwrite the other when operating within the one visual so we also need to restrict what measure we want the format string applied to. To do this we select Format String Expression from the dropdown in Tabular Editor and implement the same logic as we did previously except now for the format string to apply. Additionally, this is where you can specify the exact format you want for each measure so that percentages for example will display correctly alongside dollars.

Add a similar logic to the format string expression

Logic for the format string expression

You can read about format strings here:

https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-numeric-format-strings

Now that our first calculation group for our first slicer is complete, we can create the second calculation group for the second slicer and name it “SalesMetric2”. You can use copy paste in Tabular Editor to paste all the calculation items from the first calculation group into this new calculation group by selecting all the calculation items the choosing copy from the menu. Then select the second calculation group and choose paste.

Create new Calculation Group

Select Calculation items and choose Copy from menu

We need to restrict the second calculation group and subsequent slicer to act only upon our “Metric 2 Measure” so we can just alter the measure referenced in each calculation item changing “Metric 1 Measure over to “Metric 2 Measure”. You could use a script in Tabular Editor to do this quickly if you have a lot of measures in your calculation group. You also need to change all the format string expressions in the duplicated SalesMetric2 calculation group to reference the “Metric 2 Measure” instead of “Metric 1 Measure”.

Change the conditional logic in the duplicated calculation group

Change “Metric 1 Measure” to “Metric 2 Measure” for all the calculation items in the second Calculation Group

Make the same adjustment for all calculation items in the duplicated calculation group

Change the format string expressions to “Metric 2 Measure” as well

The end result should be two calculation groups that are identical except that the first references “Metric 1 Measure’ in every expression and the second references “Metric 2 Measure” in every expression including the format string expressions. So now that we have our two calculation groups configured, we can build our report.

To do so we create a combo column and line chart with [Measure 1 Metric] as the column value and [Measure 2 Metric] as the line value. You can choose an appropriate axis like Month Year.

Then we create the first slicer and use the “Metric” column from the SalesMetrics1 calculation group as the value and create the second slicer with the “Metric” column from SalesMetrics2 as the value. You should set both slicers to be single select as selecting two calculation groups in one slicer does not work.

Already the report works and different metrics can be selected to combine in the chart but what if you only want columns and no line or vice versa? With single select enabled on the slicers you must always have a metric selected in both slicers. The solution is to create a Blank calculation item in each slicer allowing users to choose no measure in the slicer but implementing the same logic as our other calculation items so that it works as expected.

Add a blank option for users

Combo charts are great but with data labels turned on on both the bar and line elements, it starts to look messy and the labels can overlap. It often looks cluttered and the correct label can be unclear. To get around this we can add an option for users to turn off the data labels for either the columns or the line or both. To do this, we create a disconnected table with two options: “No Bar Labels” and “No Line labels”:

Disconneted table for data label slicer

We then add an extra IF statement to each of our Format String Expressions as below. The key to making the format string produce a blank is to use a unicode blank character for the format string. You can copy it from here:
https://emptycharacter.com/
Here it is used in the Format String Expression with double quotes around it:

You repeat this for all the format strings in the SalesMetric1 calculation group then for the SalesMetric2 calculation group, you replace “No Bar Labels” with “No Line Labels” and “Metric 1 Measure” with “Metric 2 Measure like this:

Basically for the first Calculation Group (column measures) you are adding that if the measure is “Metric 1 Measure” and the user has selected “No Bar Labels” then show a blank value. For the second calculation group you make it apply to “Metric 2 Measure” when “No Line Label” is selected.

You should now have a completed report allowing users to compare different measures of their choosing in a combo chart. You can use the same technique on any chart that accepts multiple measures and can scale it up to more slicers and measure values if required without any extra complexity. Enjoy!