Clustered Bar chart

[sd_09]

Hi all, 

I am trying to make a clustered bar chart in PowerBI, similar to one that I made in excel, but cant. I have sample data below. 

In the chart, I would like to provide the averages of each of these questions split by survey type. In excel, for example, it would be, make a pivot table, the questions in "values" area (summarize values by averages), the actual values in rows and the survey type in columns. 

I have tried multiple ways in PowerBI, the closest I have reached is to having the questions as a legend and survey type as rows. Does anyone know how I can interchange this so survey type becomes the legend and the questions become rows? 

Sample Data

Candidates	Date	Q1 score	Q2 score	Q3 score	Country	Department	Survey type
1	30/5/2024	5	4	2	France	Finance	Hired
2	20/5/2024	3	7	9	Belgium	Sales	Rejected
3	11/5/2024	8	3	5	Germany	Sales	Rejected
4	28/4/2024	5	6	2	France	HR	Hired
5	15/4/2024	8	2	4	Belgium	Finance	Rejected
6	15/2/2024	2	8	7	Germany	Finance	Rejected
7	06/01/2024	7	6	9	France	HR	Rejected
8	31/5/2024	9	3	3	Belgium	Sales	Hired
9	31/5/2024	4	9	4	Germany	Sales	Hired

Thank you!!

[Paulhaha]

Hi @sd_09 

I don't know if I figured out your need but here is a solution:

With the data you provided:

• I went to PowerQuery
• I selected the Q1 score, Q2 score and Q3 score columns
• Right click > "Unpivot only selected columns" 
• I got "Attribute" and "Value" columns

Now within PowerBI

• Stacked column chart

OR to be more precise "provide the averages of each of these questions split by survey type"

Does it answer your question?

[sd_09]

Hey @Paulhaha 

Thanks for your response! Unfortunately, when I try to unpivot these columns, it gives me all the columns with error in the data set. Not sure why this is the case? Could it be because of blanks? 

[Paulhaha]

Hey @sd_09 

Its strange because it works with the data sample you provided

I tried by insterting null values in Q-Score columns and the pivot works

Actually if the Q-Score values are null, it seems that the values are not pivoted (and rejected)

If it's not that confidential, don't hesitate to send me a more realistic sample of your data (with all columns) so I can try it better in my side.

Have a nice day

[sd_09]

Hey @Paulhaha 

Here's a more realistic sample of the data. There are additional columns in the data set, but they are shouldn't cause problem. The idea would be to have Graph 1 with Q1-3; Graph 2 with Q4-6 etc. split by survey type

Thank you! 

Quarter + Year	Week	Quarter	Year	Recorded Date	Response ID	Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	Q9	Q10	Q11	Q12	Q13	Q14	Q15	Q16	Q17	Q18	Q19	Q20	Q21	Q22	Q23	survey	identity	ID	department	location	recruiter	manager	Job
Q2'2023	24	2	2023	45093	7	8	5	5	3	10	4	5	5	3	10	5	4	5	5	7	3	10	5	4		5		XYZ	survey_2	Candidate	7	HR	India	name 7	name 7	A
Q2'2023	25	2	2023	45096	8	8	7	5			3	4	4			4	4	4	4	6			4	4	4			XYZ	survey_1	Candidate	8	Sales	Belgium	name 8	name 8	B
Q2'2023	25	2	2023	45096	9	6	3	4	2	8	2	5	5	3	9	4	5	5	3	5	3	9	5	5		1		XYZ	survey_2	Candidate	9	Marketing	France	name 9	name 9	C
Q3'2023	27	3	2023	45110	52	10	5	5			2	5	5			5	5	5	5	7			5	5			5	XYZ	survey_3	Candidate	52	HR	Ireland	name 52	name 52	A
Q3'2023	27	3	2023	45110	53	8	8	3	3	10	5	4	4	2	8	4	4	4	5	7	1	6	3	4		4		XYZ	survey_2	Candidate	53	Sales	Germany	name 53	name 53	B
Q3'2023	27	3	2023	45110	54	4	2	4			4	4	5			5	5	4	3	5			5	5	4			XYZ	survey_1	Candidate	54	Marketing	UK	name 54	name 54	C
Q3'2023	27	3	2023	45110	55	6		3			5	4	4			4	4	4	4	6			4	4		4		XYZ	survey_2	Candidate	55	IT	US	name 55	name 55	A
Q3'2023	27	3	2023	45112	62	3	9	4			1	3	5			4	5	4	5	7			4	5		5		XYZ	survey_2	Candidate	62	HR	US	name 62	name 62	B
Q3'2023	28	3	2023	45117	63	9	10	5	2	8	5	4	4			5	5	5	3	5			5	5		4		XYZ	survey_2	Candidate	63	Sales	India	name 63	name 63	C
Q3'2023	28	3	2023	45117	64	8	10	4	3	10	4	5	5	3	10	5	5	5			3	10	5	5	5			XYZ	survey_1	Candidate	64	Marketing	Belgium	name 64	name 64	A
Q3'2023	28	3	2023	45117	65	7	10	4			5	4	5			5	4	4	5	7			4	5	5			XYZ	survey_1	Candidate	65	IT	France	name 65	name 65	B
Q3'2023	28	3	2023	45117	66	4	9	3	2	7	3	5	5			4	4	3	4	6			2	4		4		XYZ	survey_2	Candidate	66	Finance	Ireland	name 66	name 66	C
Q3'2023	28	3	2023	45117	79	4	10																					XYZ	survey_2	Candidate	79	Marketing	France	name 79	name 79	A
Q3'2023	28	3	2023	45117	80	2	10	5	3	10	2	4	5	3	10	5	5	5			3	10	5	5	5			XYZ	survey_1	Candidate	80	IT	Ireland	name 80	name 80	B
Q3'2023	28	3	2023	45117	81	3	2	4	1	2	2	5	2	1	2	2	1	2			1	2	2	2		4		XYZ	survey_2	Candidate	81	Finance	Germany	name 81	name 81	C

[Paulhaha]

@sd_09  Hello, I think the sample of data you provided doesn't include "survey type" column 😿 with "hired" and "rejected"

[sd_09]

@Paulhaha ahh, its renamed to "survey" now --> survey_1, survey_2, survey_3 🙂 

[Paulhaha · Accepted solution]

@sd_09  thanks

He're is a short video that demonstrates how to do the visual you want with the sample of data you provided, tell me if its good for you

[sd_09]

Hi @Paulhaha,

Thank you! It still wasnt working but I deleted the excess columns and it worked somehow? 🙂 

[Paulhaha]

@sd_09 
Hi, okay nice !