Custom Power BI visual for Line chart with two Y-Axis

Power BI support certain type of visuals that are by default available in the document. These are absolutely great and work perfectly fine, have a lot of capabilities to set properties and change the settings.

But every so often in past year, I have come across the fact that I wanted to visualize a typical line chart with two lines, each with it’s own Y-axis.

Let me give you a quick R example. First, very simple dataset, where there are two values, each with a slightly different scale: quantity in range from 499 to 760 and temperature  in range from 15 to 24.

2020-04-02 22_10_44-RStudio

So what would normally happen, when we put this two ranges on same Y-axis:

# Both on the same y-axis
plot(dataset[,3], type="l", col="red", lty=1, ylim=c(0,1000), 
ylab = "Qty and Temp")
# Add second line
lines(dataset[,2], col="brown",lty=1)
legend("topleft", c("Temperature", "Quantity"),
       col = c("red", "brown"), lty = c(1, 1))

With the following resutls:

2020-04-02 22_21_47-Plot Zoom

We see that the temperature has almost lost all the visible variance, where as, quantity still has some.

To deal with this, we need to shift one of the lines to right Y-axis. Following this, little additional coding:

par(mar = c(5, 5, 3, 5))
plot(dataset[, 2], type ="l", ylab = "Quantity",
main = "Quantity and temperature", xlab = "Date", col = "brown")
par(new = TRUE)
plot(dataset[,3], type = "l", xaxt = "n", yaxt = "n",
ylab = "", xlab = "", col = "red", lty = 1)
axis(side = 4)
mtext("temperature", side = 4, line = 3)
legend("topleft", c("Quantity", "Temperature"), col = c("brown", "red"),
   lty = c(1, 1))

And the result is much more obvious:

2020-04-02 22_26_10-Plot Zoom

And now we can see how the temperature fluctuates (for more than 10°C).

So now that we have a solution, let’s go back to Power BI.

1. Default Visual

With the same dataset, this would be “out-of-the-box” visual, available to you:

2020-04-02 22_34_34-twolines_chart - Power BI Desktop

A combination of Bar chart and line chart, each with it’s own y-axis. Great. But If I want two lines, this is not possible with out of the box visuals.

2. Using R or Python

Copying the R code from above introduction example into Power BI, literally makes it the same in Power BI. Same logic goes and applies to Python.

2020-04-02 22_40_23-twolines_chart - Power BI Desktop

It does the job beautifully.  Where is the trick with R or Python visuals. I have a couple:

  •  to many times I have seen data engineers who start to use Power BI, that R or Python is just an overkill to adopt quickly,
  • it takes coding to plot a graph and not everyone has a great idea how to tackle this issue, and
  • publishing and deploying Power BI with R or Python on on-prem Power BI reporting server will not work with neither – R or Python – visual.

This said, there is a lot of gap for improvement.

3. Downloading custom visuals

Yes, you can download a custom visual. Go ahead 🙂

4. Building your own custom visual

This area is still under-explored among the data engineers and business intelligence people. And purpose of this post is, not only to point them to start exploring on their own, but also to show them, that is not a big deal to tinker on their own.

I have done my on this website: Charts PowerBI.

So, let’s walk through how to do it.

1. Get a sample dataset. You can download (data_twoCharts.csv) mine as well from Github.

2. Go to https://charts.powerbi.tips/ and select New.

3. Drag and drop the csv file into desired Data field, and you should get the preview of data:

2020-04-02 23_14_38-PowerBi.Tips - Charts

4. Click Done.

5. The the consists of 1) blank canvas pane on right hand side and 2) Glyph pane, 3) data Pane and 4) Layers Pane.

2020-04-02 23_17_11-PowerBi.Tips - Charts

The Canvas pane can hold multiple plot segments, which we will use to generate two plots, one on top of the other.

6. Drag and drop the individual data columns (from data pane) onto canvas pane. I did, first the date column, and drag it on top of X-axis (you will see, it will snap automatically on it), and Quantity on the Y-Axis.

2020-04-02 23_23_02-PowerBi.Tips - Charts

7. Drag the Symbols in Glyph Pane. And click on  Links to  Create Links. This will automatically connect all the dots.

2020-04-02 23_25_22-PowerBi.Tips - Charts2020-04-02 23_25_38-PowerBi.Tips - Charts

8. You should get a graph like this.

2020-04-02 23_28_06-PowerBi.Tips - Charts

9. Great, half way done. Now resize the Plot Segment to reduce it to 50%. Click on blank canvas and Add anothoer Glyph, that will be associated with new Plot segment.

2020-04-02 23_36_27-PowerBi.Tips - Charts

10. Add another Plot segment (remember we are building two plot graphs, one on top of each other.)

2020-04-02 23_28_32-PowerBi.Tips - Charts2020-04-02 23_30_13-PowerBi.Tips - Charts

11. With new Plot segment, repeat the step 6, 7 and 8. Drag the Date ( to X-axis) and Temperature (not quantity) (to Y-axis) to canvas pane, drag Symbol to Glyph Pane and click Links to Create Links. And you should end up with:

2020-04-02 23_38_00-PowerBi.Tips - Charts

12. On new Plot (on right hand side), we want to switch Y-axis to right side, by clickling on Layer Pane for selected Plot Segment.2020-04-02 23_40_31-PowerBi.Tips - Charts

13. Last part is a bit finicky. With your mouse hover over the corner of left plot (and repeat with right plot), on green point and drag it over the other Plot. Yellow dotted line will appear to denote that you are expanding the graph.

2020-04-02 23_44_02-PowerBi.Tips - Charts

14. Once you do for both, there will be only “one” X-axis (one on top of the other), both Plot segments will be represented as layer on top of the layer.

2020-04-02 23_48_44-PowerBi.Tips - Charts

15. Optionally, some colour coding and graph design is super desired. This can be changed in the Layers Pane. Once you are happy and satisfied with your visual, export it as Power BI custom visual:

2020-04-02 23_48_56-PowerBi.Tips - Charts

and give it a proper name with labels for X and Y axis. These names will be visible in the Power BI document. And also, give a Custom Visual a proper name 🙂

2020-04-02 23_49_07-C__DataTK_git_DAX_Functions_04_Custom_Visual

 

Once you have done this, open Power BI and add it, the same way as all other additional/custom visuals:

2020-04-02 23_56_15-Useful_DAX_and_Power_BI_examples_for_everyday_usage.pptx - PowerPoint

From here on, you can use this visual with any dataset you want (it is not omitted to sample dataset you used for creating custom visual) and it is also ready to be published / deployed on on-prem Power BI Reporting server.

 

All code, data sample and Power BI document with custom visual are available on GitHub.

Happy PowerBI-ing. 🙂

 

 

 

 

Tagged with: , , , ,
Posted in Uncategorized
One comment on “Custom Power BI visual for Line chart with two Y-Axis
  1. […] by data_admin [This article was first published on R – TomazTsql, and kindly contributed to R-bloggers]. (You can report issue about the content on this page […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Categories
Follow TomazTsql on WordPress.com
Programs I Use: SQL Search
Programs I Use: R Studio
Programs I Use: Plan Explorer
Programs I use: Scraper API
Rdeči Noski – Charity

Rdeči noski

100% of donations made here go to charity, no deductions, no fees. For CLOWNDOCTORS - encouraging more joy and happiness to children staying in hospitals (http://www.rednoses.eu/red-noses-organisations/slovenia/)

€2.00

Top SQL Server Bloggers 2018
TomazTsql

Tomaz doing BI and DEV with SQL Server and R, Python, Power BI, Azure and beyond

Discover

A daily selection of the best content published on WordPress, collected for you by humans who love to read.

Revolutions

Tomaz doing BI and DEV with SQL Server and R, Python, Power BI, Azure and beyond

tenbulls.co.uk

tenbulls.co.uk - attaining enlightenment with the Microsoft Data and Cloud Platforms with a sprinkling of Open Source and supporting technologies!

SQL DBA with A Beard

He's a SQL DBA and he has a beard

Reeves Smith's SQL & BI Blog

A blog about SQL Server and the Microsoft Business Intelligence stack with some random Non-Microsoft tools thrown in for good measure.

SQL Server

for Application Developers

Business Analytics 3.0

Data Driven Business Models

SQL Database Engine Blog

Tomaz doing BI and DEV with SQL Server and R, Python, Power BI, Azure and beyond

Search Msdn

Tomaz doing BI and DEV with SQL Server and R, Python, Power BI, Azure and beyond

R-bloggers

Tomaz doing BI and DEV with SQL Server and R, Python, Power BI, Azure and beyond

Ms SQL Girl

Julie Koesmarno's Journey In Data, BI and SQL World

R-bloggers

R news and tutorials contributed by hundreds of R bloggers

Data Until I Die!

Data for Life :)

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

Grant Fritchey

Intimidating Databases and Code

Madhivanan's SQL blog

A modern business theme

Alessandro Alpi's Blog

DevOps could be the disease you die with, but don’t die of.

Paul te Braak

Business Intelligence Blog

Sql Server Insane Asylum (A Blog by Pat Wright)

Information about SQL Server from the Asylum.

Gareth's Blog

A blog about Life, SQL & Everything ...

SQLPam's Blog

Life changes fast and this is where I occasionally take time to ponder what I have learned and experienced. A lot of focus will be on SQL and the SQL community – but life varies.

%d bloggers like this: