R – Power BI – Add Target Marker To Each Bar in a Grouped Bar Chart

R – Power BI – Add Target Marker To Each Bar in a Grouped Bar Chart

I initially wanted to create this chart 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).

Available visual in Power BI

Here is how the visual renders with two differents Y scale.

clustered bar chart power bi

Here is how the visual renders with only one Y scale.

clustered bar chart with marker power bi

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
create R visual with Power BI

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 diamond markers

Combine Clustered Columns with Clustered Markers
# 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

cmbine bar chart and marker with R
# 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

r visual embedded into power bi
# 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

R chart dynamic legend and label
# 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

One thought on “R – Power BI – Add Target Marker To Each Bar in a Grouped Bar Chart

Leave a Reply

Your email address will not be published. Required fields are marked *