Understanding the Matrix Visualization

[dom-mitchell]

Very Rookie posting coming up, but I cannot seem to get the Matrix Vasualisation to do what I want it to do. 

I've used it before for simple, one row per column charts without a hitch but I'm now trying to do something a little more advanced and running into difficulty. 

I want to be able to so how multiple (3) rows of information for each Consultant, essentially replicating this Excel screen shot:

The data is all loaded and spread across two different tables: Master Target List and Global Deal Data.

In Master Target List there is a column called Target

In Global Deal Data there is a colum called Total £ Fee (this equates to the booked number)

I have also created two reference tables called Calendar Table and Consultant List which have relationships withboth the above tables meaning a central point of reference for Consultant Name and the dates (I hope):

As my starting point, I have added the data fields to the visualization like this:

But that is producing something that looks like this:

With actual values loaded when I expand on a consultant name rather than adding them as row headings and then the data in the corresponding months.

I'm sure I'm missing something obvious but it's driving me mad! 

Can anyone help out a bit of a rookie?

Many thanks in advance!

[Anonymous · Accepted solution]

@dom-mitchell , in the Format options for the matrix visual, go to Values, and set "Show on rows" to On.

I think you also need to remove "Total £ Fee" and "Target" from the Rows section of the Fields list. I believe they only need to be in the Values section of the Fields list.

[PaulDBrown]

@dom-mitchell 

@Anonymous  is absolutely right. Create the matrix with:

Rows: Consultant name

Columns: Month

Values: [Booked]
            [Target]

            [% Target]

Then select "Show on Rows" under Values in the format pane. (Do a search in the search box of the format pane if in doubt)

You would only need to construct a custom table for the matrix if you wanted the "Measures" to lead the row filtering

[PaulDBrown]

It looks like you need a "custom" matrix. Check out this blog post, but instead of using the custom layout as columns you add them as rows:

Creating a custom or hybrid matrix 

[dom-mitchell]

Thanks for the speedy reply @PaulDBrown ; that is going to take some reading and digesting! 

[PaulDBrown]

Let us know if you need further help. If so, please provide sample data or PBIX file (non-confidential data).