Suppose I have a combo chart with different metrics on the Y-Axis and I want to make my visualization more flexible for end users. The reason might be that not every user needs to see every metric but have the option to see them. We can set up a control that allows users to show or hide specific Y-Axis categories in the chart.
Let’s say I have a very simple chart where I am looking at Sales, Gross Profit, and Revenue by month.
X-axis; Month of Date
Y-axis; Sum of Sales, Sum of Revenue, Sum of Gross Profit
First let’s add a List Value control for the user interface. On the control settings (pic below) we can set up the text values and the corresponding display values.
Text Value | Display Value
1 | Sales
2 | Gross Profit
3 | Revenue
And please take note of the Control ID, metrics_control, for our formula to be used in the chart. We can use more user friendly name for the Control Label, Metrics.
There are 3 different metrics; which means 3 conditions and 3 calculations with a combination of If() and ArrayContains().
Let’s take Sales as our example and logically verbalize what we we need;
if the metrics_control array contains “1” then return the sum of sales, else return Null.
The Sigma formula;
If(ArrayContains([metrics_control], “1”), Sum([Sales]), Null)
If(ArrayContains([metrics_control], “2”), Sum([Sales]) - Sum([Cost]), Null)
If(ArrayContains([metrics_control], “3”), Sum([Sales] * Avg([Price])), Null)
Finally, we need to hide Null data on the chart when metric is unchecked. Navigate to the far left side, Element Format > Line Style > Null Data = Hide
Finally appreciate this awesome work you just did!
Workbook exploration!