Viewing Sales in one chart and Profit in another can provide a good overview of a store’s performance. But sometimes it’s handy to be able to see both measures in one chart.
For example, when looking at Sales and Profit by product category, we might see Category A in a Sales chart then have to manually scan a separate Profit chart to see how Category A ranks. This is a bit tedious.
Instead, we could display them ranked in a bar chart by one measure and coloured with respect to the other measure. This gives us an instant and easy way to gauge how profitable the top-selling Category is.
To combine both measures into a single chart, we’ll use parameters – two, in fact – one for each measure.
Step 1
Now, because we want to dynamically switch which measure we are viewing and which measure we are using for colours, we will create parameters to facilitate the switch.
Create a new parameter called Selected Measure to View with Sales and Profit as values in the list.
For the second parameter, duplicate the parameter you just created and rename it to Select Measure to Colour.
Finally, right-click both parameters and click Show Parameter to add the parameter selection control to your chart.
Step 2
Next, we want to create new measures that change based on which value we select in the parameters.
Create a calculated field called Selected Measure and add the following conditional statement – this will allow us to toggle the measure viewed based on the parameter value chosen (remember those values we entered into the list when creating the parameter?).
Duplicate this calculated field and rename the new one to Selected Colour. This will let us toggle the colour of the bar charts based on the measure selected.
Great! Now we’re ready to assemble our chart.
Step 3
If we were creating a normal bar chart without the option to dynamically change the measure we’re viewing or using for colours, we’d just drag Sales onto Rows and Profit onto the Colour marks.
But, instead we want to use the measure that are toggleable – so drag the calculated field you made, Selected Measure on Rows and the second calculated field, Selected Colour, onto the Colour mark.
And that’s it! You now have a chart, taking up minimal space on your dashboard, that lets you toggle between two measures for both the bars and colours.