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.
Then I found Min and Max value in both datasets. Below is example of calculation of Max value.
Completion of vizzies
- put both Total measures into Detail Marks
- from Analytics panel drag Reference line into Table
- set the line as in picture below (once for Max and second for Min)
- Replicate the same steps in second vizz
Finally I got the required result. User can select measure and the axis range for both counties is aligned.