Matrix Report

[Anonymous]

I need to create a report that shows three tables worth of data in a matrix. I don't even know where to start.

The three tables look like this.

Table: BU Team Assignent

BU Team Assignment

Table: BU Project Assignments

BU Project Assignment

Table: Project Assignments

Project Assignments

I need a table that looks like this.

BU Alignment

Basically:

• The table named BU Team Assignment defines the rows.
• The table named BU Project Assignemnts defines the columns.
• The table named Project Assignements is what defines the values of the matrix.

If it helps, here is a LINK  to an excel file with the data in it.

[jdbuchanan71 · Accepted solution]

@Anonymous 

You can get there pretty easy:

Assignment Check = IF ( NOT ISBLANK ( COUNTROWS ( Assignments ) ), "X" )

I have attached my sample file for you to look at.

[Anonymous]

I have a few formating questions.

1. Do you know of a way to rotate the text in my column headers by 90 degrees? The names of the projects are very long and I need to make the column widths smaller so the table is smaller.
2. How do you get the Xs to appear in the middle of the cell?
3. Is there a way to add dark boarder lines to show different groupings of data like in Excel? For example, having each Business Until have a dark boarder around it?
4. Can I contorl the order that the clomns are in? I can't seam to be able to reorder them.

Also, what if the assignment table looks like the image below. Being assigned to a project would mean having HRS not equal to 0.  Also, you can click this LINK  to get a new Excel file with Exmple 2 of the Project Assignment table.

[Anonymous]

OK, I answered my own question about using my new example for the project assignment table I described above. I used the following measure:

Assignment Check = IF(SUMX ('Assignments', 'Assignments'[HRS]) > 0, "X")

I could still use some help with the formatin questions above.

[v-deddai1-msft]

Hi @Anonymous ,

>>Do you know of a way to rotate the text in my column headers by 90 degrees? The names of the projects are very long and I need to make the column widths smaller so the table is smaller.

There is no such option for  rotating  column headers in matrix.

>>How do you get the Xs to appear in the middle of the cell?

By default, values is shown in the center.

>>Is there a way to add dark boarder lines to show different groupings of data like in Excel? For example, having each Business Until have a dark boarder around it?

You can choose different style for matrix

>>Can I contorl the order that the clomns are in? I can't seam to be able to reorder them.

Yes, you sort the columns by other columns before you put it in the matrix. And it will show your expected sequence. Please refer to https://www.biinsight.com/quick-tips-how-to-sort-matrix-by-column-in-descending-order/

Here is a good article for you to learn more about matrix: https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai