A while back, I was working with a customer that needed help putting together some Power BI Dashboards. I had the data shaped to meet their needs and got all their dashboards completed. It was the end of the engagement when one last “nice to have” visualization came up. Unfortunately, the data shape didn’t quite match what was needed to produce the visualization. On top of that, there wasn’t an out-of-the-box Power BI visualization for Stacked Clustered Column Charts.
One of the advantages of using the Power BI service is its built-in support for viewing and interacting with visuals developed in R, often referred to as “R visuals”. The R language provides the ability to reshape the Power BI data without having to change the underlying data model. The ggplot2 package for R provides a powerful graphics language for creating complex graphics. These two capabilities are what I needed to develop the visualization required by the customer.
Whereas, the Power BI service includes the R engine, the Power BI Desktop does not. To develop my R visual on my desktop, I first downloaded and installed the R engine. The R engine can be downloaded and installed for free from many locations, including the Revolution Open download page, and the CRAN Repository.
Power BI Desktop allows you to use an external R IDE to develop R scripts which I think is easier than developing the code in the Power BI R script editor. RStudio is a free open source integrated development environment (IDE) for R that I like. So, I downloaded and installed RStudio as well.
With both the R engine and RStudio installed, I then went about developing the R visual. I only needed two lines of code to reshape and sort the data and one line of code to graph the data, and the visualization was done!
Below is the script that produced the R visual. The ggplot2 library was used for all the complex graphing functionality. I used the reshape library to group the data differently than what was in the data model. In my data model the OverTimeHours and RegularHours were separate measures. I needed to reshape it so that OverTime and Regular were types of Hours. I used the scales library to enhance the graphics of the visual.