Rscript in PowerBI for combined boxplot with bar chart (separate y-axis)

Question

Total noob to both Stackoverflow and R so please be gentle!

I'm trying to create a single graph in PowerBI using R that combines a boxplot with a barchart on a time-series x-axis. Something that looks like this: Boxplot w Barchart (also below).

What I'm trying to achieve is an x-axis that is the week number (Weeknum) and y-axis for the barchart that is the count of 'LDS' and secondary y-axis that is a cost-per-mile range for 'CAD_CPM.'

I am able to create the boxplot using the below code but cannot seem to figure out how to add in the bar chart.

Any and all help greatly appreciated! Thanks!

boxplot (
CAD_CPM~Weeknum
,data=dataset
,main="Cost Per Mile (CAD)"
,outline = FALSE 
,ylab = "CPM (CAD)"
,xlab = ""
,col=(c("pink" , "light blue"))
    )

PowerBI boxplot (also below)

I don't know much about plotting in base R, but this post might help — 4redwood

mnm mnm · Accepted Answer · 2020-07-24T12:42:27

Since, you've not provided a minimal reproducible example, I have used the mtcars dataset in R to illustrate my point.
Try the following code in R script editor of PowerBI

library(ggplot2)
library(dplyr)

data = mtcars

temp <- data %>% 
  group_by(cyl = factor(cyl)) %>% 
  summarise(mpg = mean(mpg))
ggplot(data, aes(factor(cyl), mpg)) + 
  geom_bar(data = temp, aes(cyl, mpg), stat = "identity") + 
  geom_boxplot()+
  theme_light()

Will result in a boxplot combined with barplot as given below

Now, suppose you want to add some spice to this plot, i.e., make a colorful plot, then try the following code;

library(ggplot2)
library(dplyr)

data = mtcars

temp <- data %>% 
  group_by(cyl = factor(cyl)) %>% 
  summarise(mpg = mean(mpg))
str(temp)

ggplot(data, aes(factor(cyl), mpg)) + 
  geom_bar(data = temp, aes(cyl, mpg), stat = "identity",
           fill=temp$cyl) + 
  geom_boxplot(aes(fill=factor(gear)))+
  theme_light()

Will result in following plot;

Adding a secondary y-axis can be achieved by using the sec_axis() function. See the official docs for more info.

library(ggplot2)
library(dplyr)

data = mtcars

temp <- data %>% 
  group_by(cyl = factor(cyl)) %>% 
  summarise(mpg = mean(mpg))
str(temp)

ggplot(data, aes(factor(cyl), mpg)) + 
  geom_bar(data = temp, aes(cyl, mpg), stat = "identity",
           fill=temp$cyl) + 
  geom_boxplot(aes(fill=factor(gear)))+
  scale_y_continuous("mpg (US)",
                     sec.axis = sec_axis(~ . * 1.20, name = "mpg (UK)"))+
  theme_light()

Rscript in PowerBI for combined boxplot with bar chart (separate y-axis)

1 Answers