Axis range alignment for more charts when changing Metrics by Parameter

This week I’ll describe solution I have discovered few days before, when I worked on visualization where I wanted to compare variability of selected measures between 2 competitors (in case below I’ll use Global Superstore data and compere 2 Countries)

I wanted to meet these conditions:

allow users to select which Measure will be displayed
to show both countries next to each others (data for each country were in different dataset)
align axis range for both countries to be easy to compare them

Note: In this example I connect Orders table twice (as 2 Data Sources) and for one case set in Data Source Filter value for Country on United Kingdom and for second on United States.

Using Parameter for selecting the Measure

I wanted to use 3 measures and let user to select which will be displayed. So I created the Parameter this way:

right click into Data panel and select Create Parameter
name it “Select Measure“
set Data type as String
in Allowable values select List
in Values write name of these measures: “Profit“, “Sales“, “Shipping Cost“

After I clicked on OK the Parameter appeared in Data panel. I right clicked on it and selected Show Parameter Control to display it in my Sheet.

To be able to use selected measure in my vizz I had to created Calculated field.

And I was ready to build the vizz

put Order Date into Columns
put Selected measure UK unto Rows
put Order ID into Details
in Show Me select box-and-whisker plots

Then I repeated the same set of steps in second Data Set to create same chart for second country.

Creating the Dashboard

I put both charts into dashboard, added Title (I used Parameter also in title to make clear which measure is displayed) and did a little bit of formatting.

The last thing I had to solve was different range of axes for both chart, because otherwise was difficult to visually compare both countries.

Axis range alignment

Calculation of Measures

I decided to solve it by finding Max and Min value of Selected Measure in both countries and then use this Measures as Reference line in both charts.

First I calculated Min and Max value in each country. Below is example of calculation of Max value in UK dataset. Because I was interested in value for whole Order I aggregated measure on this level by function FIXED.