Restructuring or adding measures to transform existing data into a matrix style table

[Anonymous]

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

[v-jingzhang · Accepted solution]

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.

[Anonymous]

Thank you! This works perfectly!

[amitchandak]

@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

[Anonymous]

Additionally a bar chart of

Axis: table1[gender]

Legend: table1[]gender]

Value: Count of table2[index]

gives me two columns male and female, each with count 4

[Anonymous]

I just tried again adding the count of table2[employeeID] with table1[gender] and the pie chart is producing a 50/50 distribution of gender - which i assume it is getting due to the fact that there or 2 choices in the gender column.