Creating a Combo Chart in Power BI: understanding the multiple Y-axis

If you are experiencing some difficulty with creating combo charts in Power BI that replicate those found in Excel. Then, fear not, because this is the right blog for you. In this blog, we will delve deeper into combination charts, by using the Line and clustered column in Power BI and then adding some tweaks to it. We will also look at using both percentage and absolute values when creating the multiple axes for your chart. To kick off this topic, I will explain some of the differences that you can expect between the combo chart in Excel vs the one in Power BI, thereafter, I will take you through the steps in creating the Power BI version of this chart.

 Comparing the Excel chart with the Power BI chart: an overview

Combo chart in excel

combo chart in Power BI

It is important to note the differences in the charts above.
The Target value is represented by a line in excel, but a column in Power BI.
The Current and Target values are in percentage format in both charts, while the Absolute value is a numeric value.
In excel (left image) you can have more than one visual indicator per axis:
Stacked line and clustered column
So, you have a line and a column on the left axis.  The line represents the Target value and the columns represent the Current value, both of which are percentage types.
In Power BI, however, you can only have one type of visual indicator per axis, so here you can only have columns or lines on the left axis.  So because both the Current value and the Absolute value are percentages and both are on the left Y-axis, both will be represented as columns, but in different colours.  And on the right Y-axis, we see the Absolute value is indicated with a line.
Lets now take a look at the steps needed to create this chart in Power BI:

(Step 1) Select the appropriate chart from the visuals pane in Power BI 

combo chart in power bi

This one is straight forward, you just select the appropriate combo chart that you want.

(Step 2) Select the fields for your multiple Y-Axes

select fields for chart

When selecting the fields for your chart, make sure that you group the correct types with each other. So, in this case, the Current value and Target value are both percentages. The Current and Target value is represented by columns on the left axis. The line values indicate the Absolute value and will be plotted on the right axis.

(Step 3 ) Formatting the multiple Y-Axes

Make sure to switch on, the legend, Y-axis, X-axis, Data labels, and title of the chart.

Then for the Y-axis, we will start with the primary axis (left axis) which will plot the Current and Target values. Navigate to the Y-axis, and switch it on.

Primary axis

 

The title, in this case, will be the Current value and Target value of the KPI.

It is very important not to define a start and end value for your Y-axis in this case:

start and finish for Y axis

You can leave it on Auto, otherwise, you won’t be able to align your left and right axis later on.

Now, over to the secondary axis (right axis):

Similar to the primary axis, you need to switch on the secondary axis for your combo chart.

At this stage, you need to align the zero’s so that the chart can start plotting from zero on both sides.

For this axis, you will select Position, right, to have the absolute value shown on the right.

 

Switch on secondary axis for combo chart in power bi

 

Thereafter, you can switch on the title and name your title what you want, in this case, Absolute value:

chart title in power bi

In Summary

When creating combo charts in Power BI by making use of the Line and clustered column chart you need to make sure that:

  1. Group data types that go together on the same axis, i.e. percentage on the Primary axis, and absolutes on the Secondary axis.
  2. Don’t define a start and end value for the axis, unless you don’t want the axes to start at 0.
  3. Switch on the axis if you want to view the data type.

That’s it for now.

For other interesting blogs, you can also visit our blog site at: https://databear.com/blog/

Official Microsoft information pertaining to this topic: https:docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-combo-chart 

Visit our training page for great course options:  https://databear.com/power-bi-training/