Stacked & Clustered Bar Graph using R

[Anonymous]

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 

[Icey · Accepted 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.

[ShawnHDSB]

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

[Anonymous]

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/

[Icey · Accepted solution]

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.

[Icey · Accepted 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.