topic Re: Clustered Bar chart in Custom Visuals Development Discussion

Clustered Bar chart

sd_09 — Fri, 31 May 2024 08:54:11 GMT

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!!

Re: Clustered Bar chart

Anonymous — Fri, 31 May 2024 09:40:11 GMT

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?

Re: Clustered Bar chart

sd_09 — Fri, 31 May 2024 13:16:06 GMT

Hey @Anonymous

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?

Re: Clustered Bar chart

Anonymous — Fri, 31 May 2024 15:45:41 GMT

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

Re: Clustered Bar chart

sd_09 — Mon, 03 Jun 2024 07:45:54 GMT

Hey @Anonymous

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

Re: Clustered Bar chart

Anonymous — Mon, 03 Jun 2024 07:56:23 GMT

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

Re: Clustered Bar chart

sd_09 — Mon, 03 Jun 2024 07:59:26 GMT

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

Re: Clustered Bar chart

Anonymous — Mon, 03 Jun 2024 08:25:13 GMT

@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

Re: Clustered Bar chart

sd_09 — Thu, 06 Jun 2024 11:00:07 GMT

Hi @Anonymous,

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

Re: Clustered Bar chart

Anonymous — Thu, 06 Jun 2024 11:11:21 GMT

@sd_09
Hi, okay nice !