Forum Discussion
Restructuring or adding measures to transform existing data into a matrix style table
I have a table of people from multiple offices and projects:
Table1:
| Project# | employeeID | Office | Gender |
| X008 | 001 | A | M |
| x008 | 002 | A | F |
| x009 | 001 | A | M |
| x010 | 003 | B | F |
| x008 | 004 | C | F |
| x011 | 004 | C | F |
What I am interested in seeing, and was able to achieve with a matrix visualization is:
MatrixTable:
| employeeID | Office | Gender | ProjectCount |
| 001 | A | M | 2 |
| 002 | A | F | 1 |
| 003 | B | F | 1 |
| 004 | C | F | 2 |
However my end goal is to be able to use the count of employeeID as seen in the matrix derived table and the other columns above to create visuals for say the gender distribution in the company or number of people working on 2 projects or which offices people are working out of. I cannot see a way to use the visual to create other visuals, so I assume I must use measures somehow to duplicate the matrix columns.
I have also created a second table using a copy of the employeeID column from the original table with duplicates removed and added an index column to get an accurate employye count. This is related to the original table in a one to many relationship using the employeeID column, however PowerBI doesnt seem to be able to use this relationship using count of Index to create the visuals I am after.
For clarity the second table I created in Power Query is:
Table2:
| employeeID (duplicates removed) | Index |
| 001 | 1 |
| 002 | 2 |
| 003 | 3 |
| 004 | 4 |
Can someone please advise me on the approach I should use to solve this problem?
TIA
Hi Anonymous
You cannot use the matrix visual to create other visuals. But you can create a calculated table based on Table1 to get the same result as your matrix visual. Then you can use this calculated table to create other visuals.
MatrixTable = SUMMARIZE(Table1,Table1[employeeID],Table1[Office],Table1[Gender],"ProjectCount",COUNT(Table1[Project#]))Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
5 Replies
v-jingzhangCommunity Support
Hi Anonymous
You cannot use the matrix visual to create other visuals. But you can create a calculated table based on Table1 to get the same result as your matrix visual. Then you can use this calculated table to create other visuals.
MatrixTable = SUMMARIZE(Table1,Table1[employeeID],Table1[Office],Table1[Gender],"ProjectCount",COUNT(Table1[Project#]))Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.- amitchandak
Super User
Anonymous , bit confused table 2, should be possible with count(Table[employeeID]) or count(Table[Project #]) as measures or when you add the column to visual choose count as aggregation
