Creating a matrix in a specific way with common interests in the comparison

[lukeSDM]

Hello,

I am trying to create a matrix table where the column and row are the same with another column heading above them.

Please see the example of what I want which i created in Excel.

I have tried to do this in power bi but cannot figure out how to do this, I have also attached a picture of the data im working with to help make more sense of this.

The data being plotted would be controlled by a qualification slicer i.e english or maths.

This is what my data looks like, hopefully this might help you get a better picture of what i am trying to do!?

all help is appreciated!

Thanks

[lukeSDM]

Just to add there is a also a result set column on the end of the screenshot (  I missed it off!)

It is either autumn 1 or autumn 2 as seen in the excel exemplar screenshot.

[AllisonKennedy]

@lukeSDM  what is the value? Looks like it's counting the number of values in the adno column, but what do the 1, 2, 3, 4,... represent?

[lukeSDM]

@AllisonKennedy 

Hi,

Do you mean the 1-9 along the collumn and rows, if so they are the grade that can be achieved for each subject.

The numbers plotted in the middle would be the count of the adno number, correct!

Thanks

[AllisonKennedy]

So do you want to count adno in autumn1 grade 3 and if that is equal to the count of adno in autumn2 grade 3, then put the count in the table, and if they're not equal leave blank?

[lukeSDM]

@AllisonKennedy 

Not entirely if a student achieves a grade 3 in autumn 1 and a grade 4 in autumn 2 then they go in that box and if another student achieves the same grade they go in a different box E.G. 7 in autumn 1 and a 7 in autumn 2

I will attach another screenshot of the excel example to try and make it clearer.

the adno will be the count of the studens who achieved that specific grade combination!

[AllisonKennedy · Accepted solution]

@lukeSDM  Thanks for the clarification. What you're trying to do is pivot the data so that you can plot the attribute of autumn1 and autumn 2 as separate columns. 

In the Power Query Editor, select the season column. In the Transform tab, click Pivot. Choose Grade for the Values column and expand Advanced Options. Select Don't Aggregate from the dropdown. 

the formula bar (can be turned on in the View tab) should look something like this: 

= Table.Pivot(Source, List.Distinct(Source[Season]), "Season", "Grade")

Then use Autumn1 in the Rows and Autumn2 in the columns and Count of Adno in the values. Then add the filter for Qualification. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[lukeSDM]

@AllisonKennedy 

Sorry to come back to this again, but I do not suppose you know a way to have the column headings above the grades and by the rows?

I would like a heading above the numbers so we know what term we are comparing against!

[AllisonKennedy]

@lukeSDM  that looks really cool! There's not an easy way to get the column headers in the way you had them in Excel, but you can simply add text boxes into the report if that would work? Otherwise you can get something that looks similar to this: 

I did that by creating two new columns, Autumn 1 Heading = "Autumn 1 Heading" (I created it in Power Query Editor actually by adding custom column), and Autumn 2 Heading = "Autumn 2 Heading". I then added them to the matrix above the Autumn 1 and Autumn 2 columns we created. Not very elegant solution, but kind of works. 

To Get Autumn 1 to show to the side, you can turn the stepped layout to 'off' for row headers of the matrix. 

Otherwise if text boxes will work just use that. 

[lukeSDM]

Thank you i just used the text box and grouped it all together!

[lukeSDM]

HI,

Sorry to re open this issue so quickly but my slicer selection for the qualification is not changing the numbers plotted ?

I have checked the relationship and all seem to be okay but I am struggling to see why it is not changing?

[AllisonKennedy]

@lukeSDM 

What's the relationship? Maybe open a new thread and provide screenshot of your relationship schema. In the mockup I did to help solve your problem, everything was only in one table, so no relationships needed. 

[lukeSDM]

@AllisonKennedy 

I had to duplicate my table as all of the other visuals I had created from the data set had broke.

[AllisonKennedy]

@lukeSDM  fair enough. Do you have a Dimension table for qualification now then?

[lukeSDM]

@AllisonKennedy 

Im not entirely sure what that means (Newbie Alert) 

Interestingly enough when I change the slicer to the qualification column on the duplicated table the values change in the matrix.

So somewhere, something is not linking up ?

Very confused 

[AllisonKennedy]

@lukeSDM  Create a new query so others can find it easier and tag me in it. If you are able to send screenshot of your model view, that would be really handy, but otherwise I'll reply with a quick overview of what I mean by dimension table and some relationship best practice now that you have a few tables to work with. 🙂 

[lukeSDM]

@AllisonKennedy 

Genius !

Thank you veruy much!