Ever need two different scales on the Y-Axis of a line chart? If so, then this tutorial is for you. While creating a dual y-axis line chart is pretty common in excel, it is not as easy in power BI. The only standard chart that comes with Power BI Desktop that enables dual y-axis is the Column and line combo chart types.
For this particular visual I needed to show correlation between two time series with different Y-axis scales. The Y-axis on the left of the chart had data elements in the thousands, but the right side needed percentages. The tutorial below illustrates how to accomplish by building a custom visual using the Charts.PowerBI.Tips tool.
Video Tutorial
note: there are a bunch of really good custom visuals that can be downloaded from the Microsoft App Source store. However, this article will not review all third party visuals that are able to produce a dual Y-axis line chart.
Source files
All files used to create this visual are located here on GitHub.
Layout file
The file used in this tutorial was a derivation of the Sunset layout from PowerBI.Tips. If you like this file, you can download it here:
If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.
Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
Check out the new Merch!
Very nice video! Is it possible to have 4 lines in the visual? 2 for each y axis?
Yes this would be possible, you would need a new column to the dataset that would be able to distinguish between the two lines for each plot segment.
Thanks for sharing, very useful.
Is it possible to build a custom chart with stacked bar and a column to display two different measures on X-axis.
I think this would be possible. Here is a tutorial on how to build a stacked bar chart. https://powerbi.tips/2019/11/stacked-bar-chart/
To get this to work you might have to layer multiple charts together to get the desired result. Similar to how we stacked the two charts in the dual y-axis tutorial.
I absolutely love this! ALMOST exactly what I needed. A few questions regarding capabilities:
1 – Can we show the data values?
2 – Can we add a legend?
3 – Can we rename the columns? (The visual wants to put _ between words in the column names.)
1. Yes, you need to add a text box to the dots. Do this by adding a text box to the glyph. Then drag the data value to the added text box in the glyph. You can add the text onto the dots and then even change the rotation if needed.
2. Yes, there are a couple ways of doing this, the easiest being you add some marks & text boxes into the plot area. Instead I would recommend that you build a complimentary chart in power BI and place it near or on top of the corner of the custom visual. This will give you the ability to select the different legend items and have them filter the line chart.
3. No, you can’t rename the columns in Charts.PowerBI.tips. Instead you would want to rename them first in Power BI Desktop prior to exporting. Then you can use them in Charts.PowerBI.Tips
Thank you for this. It works perfectly as long as I only have data for one year. When I have more than one year’s data, I get one line per year. I need to be able to show a multiple year trend. How do I adjust the visual for that?
In the video I used the column labeled month. In order to get year over year trending, you would need to have a different column with mm/dd/yyyy formatting on the x-axis. Also in the example I was using a date hierarchy. You might try exporting your data from PowerBI desktop without the date hierarchy. This will enable you to have over year trending.
Hi, thanks for this! I’m coming across an issue where if I my data is organized by weekly dates, like 10/1/19 and 10/8/19, when I import the visualizations into Power BI the dates show up as long numbers like 1568530800000. Do you know why this might be happening and how to work around it? Thanks!
The charts program tries to make dates a number by default. This is not ideal when working with date time periods. I have found that charts like dates being handled more like text fields. Seems to yield better results. Try changing your date field to Text then importing. You will need to add a sort by column to get the dates to sort out in proper order.
that is a great work. thank you for this.
Is there any way to define type of data when we import csv file? I have a column of day in a month, when I imported it, it is considered as numerical data not categorical data.
You would want to adjust this in the data model, after you load the data from the Power Query. You can adjust this setting by changing the column default summarizing to Don’t summarize. See the following article on how to do this: https://www.sqlchick.com/entries/2017/11/22/why-the-default-summarization-property-in-power-bi-is-so-important