R – Power BI – Add target marker for each bar in a Grouped Bar Chart
I initially wanted to create a grouped bar chart with a target marker for each Bar in power BI but so far Power BI does not support such visual so I decided to create it with R and share it.
I needed to have a clustered column chart and to add a marker over every single column, however, in Power BI we can only have a marker over the shared axis (Group) and not over the column series (Subgroup).
Table of Contents
Available visual in Power BI
Here is how the visual renders with two differents Y scale.
Here is how the visual renders with only one Y scale.
As we can see the markers do not cluster the way the column do they all line up over the middle of the cluster of columns they represent.
What I need is to have the markers over every single column.
So as Power BI does not have this capability yet I decided to create my own visual using R.
In a previous post, I already shared how we can create a visual using python. so this time let’s do it in R!
Requirements
The requirements were to compare companies’ performance across their different business region and also giving the ability to see if they have reached their target still across their business region.
In fact, a company can perform very well in the West while it can perform poorly in the East.
All this information has to be displayed in a single visual and at the moment of writing this post, there is no possibility to achieve this with Power BI (no such custom visuals exist either).
Create R visual in Power BI
So let’s create this chart in Power BI using R.
- Click on R script visual
- Drag your needed columns
Then once the needed columns have been added to the visual copy/paste the following script replace my columns by your columns and adapt it as you wish.
Grouped Bar Chart with target marker for each bar
# Paste or type your script code here:
library(ggplot2)
library("dplyr")
##################################################################
#replace your column names here
names(dataset)[names(dataset) == "your col1"] <- "Group"
names(dataset)[names(dataset) == "your col2"] <- "Subgroup"
names(dataset)[names(dataset) == "your col3"] <- "Score"
names(dataset)[names(dataset) == "your col4"] <- "Target"
##################################################################
dodge <- position_dodge(width=0.9)
dataset$Colour=ifelse(dataset$Score>=dataset$Target,"1","0" )
dataset$diff=dataset$Score-dataset$Target
p=ggplot(data=dataset, aes(fill=Subgroup, y=Score, x=Group))
p+geom_bar(position=dodge, stat="identity") +
geom_point(data=dataset,aes(x=Group,y=Target,group = Subgroup),position=dodge,size=12,shape=18,show.legend = FALSE)+
geom_label(inherit.aes = FALSE,
data = . %>%
group_by(Group,Subgroup) %>%
summarise(Total = sum(diff, na.rm = TRUE),Target = max(Target, na.rm = TRUE)),
aes(label = paste(ifelse(Total>=0,"+",""), Total),x= Group,group = Subgroup,y=ifelse(Total>=0,Target+10,Target-10)),
show.legend = FALSE,
# this is the line that fixes it
position = position_dodge2(width = 0.9),size=7)+
scale_fill_brewer(palette = "Dark2") +
theme_minimal(base_size = 22)
For the label I’ve created a column directly in my R script but we can also create it in DAX “Diff = Score – Target”.
For the libraries “ggplot2” and “dplyr” you will need to install them first but once publish to the portal you don’t need to worry about it as Power Bi Service supports already most of common R packages.
Let’s add some colours
# Paste or type your script code here:
library(ggplot2)
library("dplyr")
##################################################################
#replace your column names here
names(dataset)[names(dataset) == "your col1"] <- "Group"
names(dataset)[names(dataset) == "your col2"] <- "Subgroup"
names(dataset)[names(dataset) == "your col3"] <- "Score"
names(dataset)[names(dataset) == "your col4"] <- "Target"
##################################################################
dodge <- position_dodge(width=0.9)
dataset$Colour=ifelse(dataset$Score>=dataset$Target,"1","0" )
dataset$diff=dataset$Score-dataset$Target
p=ggplot(data=dataset, aes(fill=Subgroup, y=Score, x=Group))
p+geom_bar(position=dodge, stat="identity") +
geom_point(data=dataset,aes(x=Group,y=Target,group = Subgroup,color =Colour),position=dodge,size=12,shape=18,show.legend = FALSE)+
geom_label(inherit.aes = FALSE,
data = . %>%
group_by(Group,Subgroup) %>%
summarise(Total = sum(diff, na.rm = TRUE),Target = max(Target, na.rm = TRUE)),
aes(label = paste(ifelse(Total>=0,"+",""), Total),x= Group,group = Subgroup,y=Target-10,color = ifelse(Total>=0,"1","0")),
show.legend = FALSE,
# this is the line that fixes it
position = position_dodge2(width = 0.9),size=7)+
scale_fill_brewer(palette = "Dark2") +
theme_minimal(base_size = 22)
Then for the colour I created another binary column “dataset$Colour=ifelse(dataset$Score>=dataset$Target, “1”, “0” )” so then we can display a green marker if the target is reached and red if it’s not reached.
Let’s change the shape
# Paste or type your script code here:
library(ggplot2)
library("dplyr")
##################################################################
#replace your column names here
names(dataset)[names(dataset) == "your col1"] <- "Group"
names(dataset)[names(dataset) == "your col2"] <- "Subgroup"
names(dataset)[names(dataset) == "your col3"] <- "Score"
names(dataset)[names(dataset) == "your col4"] <- "Target"
##################################################################
dodge <- position_dodge(width=0.9)
dataset$Colour=ifelse(dataset$Score>=dataset$Target,"1","0" )
dataset$diff=dataset$Score-dataset$Target
p=ggplot(data=dataset, aes(fill=Subgroup, y=Score, x=Group))
p+geom_bar(position=dodge, stat="identity") +
#geom_point(data=dataset,aes(x=Group,y=Target,group = Subgroup,color =Colour),position=dodge,size=10,shape=18,show.legend = FALSE)+
geom_point(data=dataset,aes(x=Group,y=Target,group = Subgroup,color =Colour),position=dodge,size=40,shape=45,show.legend = FALSE)+
geom_label(inherit.aes = FALSE,
data = . %>%
group_by(Group,Subgroup) %>%
summarise(Total = sum(diff, na.rm = TRUE)),
aes(label = paste(ifelse(Total>=0,"+",""), Total),x= Group,group = Subgroup,color = ifelse(Total>=0,"1","0")),
y=1,show.legend = FALSE,
# this is the line that fixes it
position = position_dodge2(width = 0.9),size=7)+
scale_fill_brewer(palette = "Dark2") +
theme_minimal(base_size = 22)
For the list of available shape you can refer to http://sape.inf.usi.ch/quick-reference/ggplot2/shape.
Let’s move the labels
# Paste or type your script code here:
library(ggplot2)
library("dplyr")
##################################################################
#replace your column names here
names(dataset)[names(dataset) == "your col1"] <- "Group"
names(dataset)[names(dataset) == "your col2"] <- "Subgroup"
names(dataset)[names(dataset) == "your col3"] <- "Score"
names(dataset)[names(dataset) == "your col4"] <- "Target"
##################################################################
dodge <- position_dodge(width=0.9)
dataset$Colour=ifelse(dataset$Score>=dataset$Target,"1","0" )
dataset$diff=dataset$Score-dataset$Target
p=ggplot(data=dataset, aes(fill=Subgroup, y=Score, x=Group))
p+geom_bar(position=dodge, stat="identity") +
geom_point(data=dataset,aes(x=Group,y=Target,group = Subgroup,color =Colour),position=dodge,size=40,shape=45,show.legend = FALSE)+
geom_label(inherit.aes = FALSE,
data = . %>%
group_by(Group,Subgroup) %>%
summarise(Total = sum(diff, na.rm = TRUE),Target = max(Target, na.rm = TRUE)),
aes(label = paste(ifelse(Total>=0,"+",""), Total),x= Group,group = Subgroup,y=ifelse(Total>=0,Target+10,Target-10),color = ifelse(Total>=0,"1","0")),
show.legend = FALSE,
# this is the line that fixes it
position = position_dodge2(width = 0.9),size=7)+
scale_fill_brewer(palette = "Dark2") +
theme_minimal(base_size = 22)
Similarly to the colour I used an expression to position the label above or under each marker “ifelse(Total>=0,Target+10,Target-10),color = ifelse(Total>=0,”1″,”0″))”.
Final note
When Power Bi does not support a specific type of visualization R or Python comes in very handy to create your own chart and even though they render as an image they have the advantage to be highly customizable and they still interact with your slicers!
If you’d like to have a similar chart directly available in Power BI you can vote for the idea here: https://ideas.powerbi.com/ideas/idea/?ideaid=5e6b5d2f-8a56-470c-bbe0-a73e1381eb2b
6 thoughts on “R – Power BI – Add target marker for each bar in a Grouped Bar Chart”
Hi Ben,
Happy New Year. I’m Alex. This is exactly what I’m looking for. Thank you so much.
Hi Alex,
You’re welcome 🙂 and I’m glad you found this post helpful
Hi Ben,
Great solution for something I’ve been struggling with. I’d have to reshape my ‘target data’ a bit for this to work, but that’s fine.
Thanks for coding this!
Hi Erik,
Thanks for your comment I’m glad you find this post useful
Hello Ben,
This visual would be perfect for me, but I am getting an error saying: replacement has 0 rows, data has 21. I am very new to R Script, so wondering if you could help me.