Secondary Axis: Comparing Measures with Different Scale
by Kenny Rencher , Technical Product Marketing
Charts are a great way to visually compare and analyze important trends in your data. Unfortunately, we often find ourselves wanting to compare measures with completely different scales. Traditionally, if you want to compare measures with different scales, you’re stuck with having to create two separate charts to get any meaningful information out of it. Thankfully, BI Office gives you the ability to integrate that information into one single chart for a better understanding of what your data is telling you.
Using Secondary Axis
The Secondary Axis option in Data Discovery lets you easily integrate two measures with two different scales into one chart by moving one of the measures to a separate Y-axis and overlapping the result. This makes it easy to compare important information, like sales and margin, without having to create a separate chart for each.
Say you’ve got a grid of Product Categories:
Figure 1: Grid of Product Categories
You want to compare your Sales and Margin by Product Category. The value for Sales is much higher than the percentage value for Margin. You could set this up as two separate charts, but your comparison will be visually limited and you cannot evaluate the trends as clearly as if it was in the same chart. When you initially put them in the same chart without switching Margin to a secondary axis, Margin is too small to show up on the chart and it ends up being a completely useless comparison.
Figure 2: Original Chart Without Secondary Axis
To fix this and analyze both measures together, right-click on Margin in the chart legend, select Series Options, and Move to Secondary Axis.
Figure 3: Moving Margin to Secondary Axis
This gives you a great comparison of Sales and Margin where the trends for each are easily identifiable. Margin will stay on the Secondary Axis while you manipulate, drill down, and even forecast out your data. Making it extremely easy to compare with sales, even though it is on an entirely different scale.
Figure 4: Comparison of Sales and Margin with Margin on Secondary Axis
Enhancing Your Secondary Axis
There are many different ways to visually enhance the measure on the Secondary Axis. To edit, right click on Margin in the chart legend again, select Series Options, and Edit Series.
Figure 5: Editing Secondary Axis Series
Now you can change the Chart Type for the series, edit the color, apply trends, edit the trend options, and edit the data labels. Depending on the trend you want to produce, especially if it is over a large time period, changing the series to Line on top of the original bar chart series can be extremely useful.
Figure 6: Switching Secondary Axis to Line Chart
Comparing measures with different scales in a chart is no longer a difficult task, and it no longer requires you to create separate charts. With BI Office’s Secondary Axis feature, you can compare these measures with just a few clicks in one chart.