Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

v-yiruan-msft

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:

yingyinr_0-1629190091872.png

Expected result:

yingyinr_1-1629190091878.png

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.

yingyinr_2-1629190147568.png

 

Preparation:

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

yingyinr_3-1629190197116.png

 

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)yingyinr_5-1629190347657.png

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.

yingyinr_6-1629190397040.png

yingyinr_7-1629190397042.png

 

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])

yingyinr_8-1629190397043.png

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

yingyinr_10-1629190609696.png

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)

yingyinr_11-1629190637222.png

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

 

Author: Yingying Ruan 

Reviewer:  Kerry & Ula