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.
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).
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).
You will get a message that the custom visual is imported and the visual icon appears below the VISUALIZATIONS pane.
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.
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.
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).
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.
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/