Dual Axis Line Chart in Power BI - Excelerator BI

Dual Axis Line Chart in Power BI

As most readers of my website would surely know, Microsoft Excel has had a dual axis line chart as a standard charting visual for many many (many) years.  A dual axis line chart is needed when you have 2 sets of data to display with significantly different scales like the one shown below.

Dual Axis Line Chart In Excel

Unfortunately, Power BI does not support a dual axis line chart as a standard visual at this time. Update 11 Mar 2020: Microsoft released a dual axis line chart as part of the Mar 2020 Desktop update.  The good news however is there is a custom visual called “Multiple Axes chart by xViz” that can do this in Power BI.  This visual has been around for a while, but there have been some formatting issues (in my view) that prevented it being a solution to this problem – that is now fixed).  I will demonstrate how to set up a dual axis charge using the Adventure Works database and this visual.

Sales Data and Margin % on the Same Chart

I have a Power BI workbook with the Adventure Works data loaded into the data model.
I have written the following measures.

Total Sales = SUM(Sales[ExtendedAmount])
Total Cost = SUM(Sales[TotalProductCost])
Total Margin = [Total Sales] - [Total Cost]
Margin % = DIVIDE([Total Margin], [Total Sales])

I will place the Total Sales on one Y axis and Margin % on the other Y axis with the X axis showing the trend over time.

Importing the Custom Visual into Power BI Desktop

To import the Multi Axes Chart custom visual, click on the overflow menu in the VISUALIZATIONS pane (#1 below) and then on Import from marketplace (#2 below).

Dual Line Chart 6

In the Power BI Visuals dialog, click on MARKETPLACE (#1 below), select Multiple Axes Chart – XViz (#2 below) and then click on Add (#3 below).

Dual Axis Line Chart 1

You will get a message that the custom visual is imported and the visual icon appears below the VISUALIZATIONS pane.

Dual Line Chart 8

Next, you can pin the custom visual to the VISUALIZATIONS pane by right-clicking on the custom visual and then clicking on Pin to visualizations pane (#1 below).  This will mean the custom visual will always be available to you when you next create a Power BI Desktop file from scratch without the need to import it again.

Dual Line Chart 9

How to Create a Dual Axis Line Chart

  • Add the Multiple Axes Chart visual to the report canvas.
  • Add CalendarYear to Axis
  • Add Total Sales to Value Axis 1
  • Add Margin % to Value Axis 2

You will get a dual axis column chart to begin with as shown below.

Dual Line Chart 10

Note that you can have up to 5 Y-axis, hence the name Multiple Axes Chart.

  • Click on Format (the paint roller icon) and choose Series Type (#1 below) as Line for both Total Sales (#2 below) and Margin % (#3 below).

Dual Line Chart 11

And that’s it. I have the dual axis line chart in Power BI.

You have plenty of formatting options.

I changed the data colours, put a Filter on CalendarYear to show 2003 data, added MonthName to Axis and CalendarYear to Legend.

Dual Axis Line Chart 2

This chart clearly displays Total Sales and the trend of Margin % over time.

Here is the sample workbook that I used – dual axis line chart sample.

Here is a link to the custom visual https://xviz.com/visuals/multi-axes-chart/

8 thoughts on “Dual Axis Line Chart in Power BI”

  1. This is great. Is there a away to adjust the spacing on the X Axis, so that you don’t need to scroll for the entire view? For example I have 2 line charts that need to at Month/ Year Granularity for 5 years. I’ve tried Date binning with 2 month groups. The point seem rather far apart and could save some space if they were able to be spaces closer together.

  2. The technique is fine, but I think it’s a good opportunity to raise the subject of how data should be shown in charts.
    Infographic conservative rules are against Dual Axis Charts.

    In Excel there is no respect to Infographic rules, and many of the graphs you see are full of Infographic mistakes.
    Power BI team are much more aware of this issue and I think rightly so.

    There are many links if you search the web that talk about it (try this: why not have Dual Axis)

  3. Thanks Matt. Such a simple yet powerful visualisation. Maybe a day will come when PowerBi visuals look as good as ThinkCell and have the ability to add chart notations

  4. Thanks Matt. Such a simple yet powerful visualisation. Maybe a day will come when PowerBi visuals look as good as ThinkCell and have the ability to add chart notations

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top