Matrix using multiple data options for rows

[TraceyHowarth]

I am a complete newbie - sorry.

I want to be able to create a matrix with data like below

	Year 1	Year 2	Year 3
Girls	44%	45%	34%
Traveller	50%	100%	34%
FSM	67%	66%	89%
SEN	0%	0%	10%

The column headings are a single data option in one of my tables but the rows are multiple data options in a table. My first question is how do you get the matrix to look like the above using multiple data options?

Secondly, the rows could have more than one option to be combined. For example Traveller could be Gypsy Roma, Occupational or Other; SEN could be K or E, FSM only needs to be T not F. The percentages come from results in another table.

I need the visual to show year %ages for each of the groups specified, eg how many girls achieved a specific grade, how many travellers (all the options) achieved the grade, how many SEN (K or E but not N) achieved the grade etc. Is this even possible in Power BI Desktop?

Any help (in laymans speak) would be gratefully received.

Tracey

[v-kelly-msft]

Hi @TraceyHowarth ，

What is your current data model？Can you make a simple sample to me if it's not confidential?

 
Best Regards,
Kelly

[TraceyHowarth]

How do I attach my model for you?

I'm sorry I am really new to all this.

[MFelix]

Hi @TraceyHowarth ,

you need to add a onedrive, googledrive, dropbox or wetransfer link or similar.

[MFelix]

Hi @TraceyHowarth ,

Depending on how your model is setup the matrix can be achieve in different ways.

Assuming you have a column for the Values in the Rows (girls, travelleer...) and another column for the years if you place this columns on the Rows and Columns of the matrix and the % on the values (this should be a calculated measure probably) of the matrix should give you this look and feel.

Regarding the second question I'm assuming you have a second level below the rows values if you had several levels in the rows and/or columns you will be abble to have an hierarchy that allows you to drill up and down your data, using slicer you can even do it more interactive.

Again this depends on the model you have. If you can share some mockup data and setup of your model the answer could be more precise.

[TraceyHowarth]

Hi Miguel,

Thank you for your response but Im not sure I explained myself well enough.

My values for girls, travellers etc all have their own columns in the table (girls actually are one of the options under the gender data colum), they are not all in one column. I understand, if they were all in one column it would work quite well. When you add more than one data column to the row area of the matrix it doesn't end up looking anything like I want.

I will gladly share my data and model but it is on another machine so I will have to copy it over to this one to share with you. That will have to be tomorrows job!