Dual Y axis values in one grouped column chart

Scenario:

The user has the sample data as below, he needs to display the sum of Amount for each product in the clustered columns on first Y-axis. The second Y-axis should display the sum of Target Amount for each product in the bubbles/dots.  X-axis is from Date field.

Sample data:

Expected result:

Detailed steps:

Analysis:

With the default line and clustered column chart, we only are able to display the bars for each legend product (P1, P2, P3 and P4) but not the pink dot (Target Amount). There is no suitable default visual and third part custom visual in Microsoft AppSource… We therefore create this chart using R scripts.

Preparation:

1. Install R and enable R visuals in Power BI Desktop (Please refer to  this official documentation)

2. Install the libraries ggplot2 and dplyr

• Launch R console
• Type the below command to install the package
  

  install.packages("ggplot2") install.packages("dplyr")

• Choose the CARN mirrors(please choose a mirror which is closer to your geographic location)

Create R visual

1. Select the R Visual icon in the Visualization pane to add an R visual and click “Enable” button when enabling script visuals window that appears.

2. Create a calculated column to get the YearMonth (Later it will be put on X axis of visual)

YearMonth = YEAR('Table'[Date])&"-"&"M"&MONTH('Table'[Date])

3. Drag the fields from the Fields pane that you want to consume in your R script in the Values section of the Visualization pane

4. Type the below scripts in R script editor

# The following code to create a dataframe and remove duplicated rows is always executed and acts as a preamble for your script: 

# dataset <- data.frame(YearMonth, Product, Amount, Target_Amount)
# dataset <- unique(dataset)

# Paste or type your script code here:
#load ggplot2 and dplyr packages
library(ggplot2)
library(dplyr)

#Set the width of dodge
dodge <- position_dodge(width=0.9)

#Set the color base on the specific condition (Amount>=Target_Amount)
dataset$Colour=ifelse(dataset$Amount>=dataset$Target_Amount ,"1","0" )

#Get the difference between Amount and Target_Amount
dataset$diff=dataset$Amount-dataset$Target_Amount

#Draw bar graph
p=ggplot(data=dataset, aes(fill=Product, y=Amount, x=YearMonth))
p+geom_bar(position=dodge, stat="identity") +

#Draw dot plot
geom_point(data=dataset,aes(x=YearMonth,y=Target_Amount,group = Product,color =Colour),position=dodge,size=8,shape=18,show.legend = FALSE)+

#Draw label
geom_label(inherit.aes = FALSE,
data = . %>%
group_by(YearMonth,Product) %>%
summarise(Total = sum(Target_Amount, na.rm = TRUE),Target = max(Target_Amount, na.rm = TRUE)),
aes(label = Total,x= YearMonth,group = Product,y=ifelse(dataset$diff>=0,Target+160,Target-160),
),
show.legend = FALSE,
position = position_dodge2(width = 0.9),size=5)+

#Set the color of the graph
scale_fill_brewer(palette = "Dark2") + 

#Set the theme
theme_minimal(base_size = 22)

Finally, we get the results we want and are able to achieve these specific needs of the user.

Author: Yingying Ruan 

Reviewer:  Kerry & Ula