I want to visualise a Clustered & Stacked Bar Chart in Power BI but Apparently it is not possible.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/6804910-clustered-stacked-chart

Sounds like it’s possible in Excel and also has been developed as a custom visual but is not available to download in Power BI for me?

http://www.defteam.com/stacked-clustered-bar-chart/

Basically I want to show budget against actual for the last 12 months, grouped by month. Then show a breakdown within each bar based on service type (paint, panel, parts & other). Below shows what I could acheive using the OOTB Line & Clustered Column bar chart visual in Power BI.

So in frustation I have started looking at what can been doing using R and gplot2 and incorporating in Power BI.

It looks like it could be acheived if I can determine how to get my data in the correct format.
http://theduke.at/blog/science/beginners-guide-to-creating-grouped-and-stacked-bar-charts-in-r-with-…
I think I could either pivot the Budget & Actual values from columns to rows or create groups\factors in R prior to visualizing.

Any suggestions would be greatly appreciated.

Cheers
@Anonymous

Solved! Go to Solution.

Hi @Anonymous ,

You need to change your data frame like below:

Then, you can refer to the article to create a Stacked & Clustered Bar Graph using R.

The code is below:

>install.packages(“ggplot2”)

>library(ggplot2)

> data<-read.table(file=”Budget.txt”,header=T)

> ggplot(data=data, aes(x=Month, y=Value,fill=Category)) + geom_bar(stat=”identity”)

> data2<-with(data, data[order(Month, Category, Actual.Budget),])

> ggplot(data=data2, aes(x=Actual.Budget, y=Value,fill=Category)) + geom_bar(stat=”identity”)+facet_grid(~Month)

Best Regards,

Icey Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @Anonymous ,

In Power BI, you can write your R script like so:

library(ggplot2) data<-dataset data<-with(data,data[order(Month,Category,ActualBudget),]) ggplot(data=data, aes(x=ActualBudget, y=Value,fill=Category)) + geom_bar(stat=”identity”)+facet_grid(~Month)

This is my sample file.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

The current data I have os of a survey for 2020 and 2021. I want to create a comparison from last year to this year .

the question has responses 1-strongly agree, 2- agree, and 3- disagree.

I am unable to create a cluster bar chart that would link on the student ids from last year and this year. It masks out the responses from student from last year to students to this year. It should show the exact percentage of last year for eg. 2-agree to this year same scale. The percentages are calculated as 100% for both the years I want the percentage to be 100% for 2020 and 100% for 2021 separately

ID Q1 Year
1 1 2021
2 1 2021
3 1 2021
4 2 2021
5 2 2021
6 2 2021
7 2 2021
8 2 2021
9 3 2021
10 3 2021
1 1 2022
2 1 2022
3 2 2022
11 2 2022
12 2 2022
13 2 2022
14 2 2022
15 2 2022
16 3 2022
17 3 2022

You can use this method as well which simulates a combined cluster and stacked chart using the built in stacked chart in Power BI.

https://www.villezekeviking.com/how-to-combine-a-clustered-and-stacked-chart-in-power-bi/

Hi @Anonymous ,

In Power BI, you can write your R script like so:

library(ggplot2) data<-dataset data<-with(data,data[order(Month,Category,ActualBudget),]) ggplot(data=data, aes(x=ActualBudget, y=Value,fill=Category)) + geom_bar(stat=”identity”)+facet_grid(~Month)

This is my sample file.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @Anonymous ,

You need to change your data frame like below:

Then, you can refer to the article to create a Stacked & Clustered Bar Graph using R.

The code is below:

>install.packages(“ggplot2”)

>library(ggplot2)

> data<-read.table(file=”Budget.txt”,header=T)

> ggplot(data=data, aes(x=Month, y=Value,fill=Category)) + geom_bar(stat=”identity”)

> data2<-with(data, data[order(Month, Category, Actual.Budget),])

> ggplot(data=data2, aes(x=Actual.Budget, y=Value,fill=Category)) + geom_bar(stat=”identity”)+facet_grid(~Month)

Best Regards,

Icey Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Find out more about the August 2023 Update.

Join Microsoft Reactor and learn from developers.

Power BI release plans for 2023 release wave 2 describes all new features releasing from October 2023 through March 2024.

Share your Data Story with the Community in the Data Stories Gallery.

User Count
129
93
72
54
50
User Count
192
108
108
71
63

You are watching: Solved: Stacked & Clustered Bar Graph using R. Info created by GBee English Center selection and synthesis along with other related topics.