Stacked Area Chart - Data Structure

[Aimeeclaird]

Hi all, hopefully I can explain my question clearly enough - apologies in advance if not, i'm new!

I have a data set which is based on information collected from a questionnaire completed by students every quarter. 

I want to compare in a visual (i'm thinking a Stacked Area Chart but open to suggestions) each assessment question's average score by;

• Region
• School
• Individual student (I have a slicer on the page to select the student you want to review visualisations for)

My data looks like this:

Student ID	School	Region	Question 1	Question 2	Question 3	Question 4	Question 5
BA1	BA	NE	1	3	3	3	4
BA1	BA	NE	3	3	3	4	4
DD2	DD	TS	4	4	4	4	5
DG4	DG	TS	5	5	3	5	4
DG3	DG	TS	2	4	5	3	4
GG1	GG	NE	5	4	4	3	3
GH2	GH	NE	3	3	3	3	3
RF1	RF	NE	3	3	3	3	3
RF1	RF	NE	4	4	5	4	4

[v-juanli-msft · Accepted solution]

Hi @Aimeeclaird 

As suggested above, unpivot other columns,

matrix above shows total average scores per region and school,

clustered column chart shows every question's average scores per region or school.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Aimeeclaird , a Better option is to unpivot data and use it

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

[Greg_Deckler]

@Aimeeclaird - So typically in that case you will want to unpivot your question columns. At that point achieving what you want should be trivial.