Create one table with split columns for each field values

[mlim0806]

Hi Community, 

I'm trying to create a table that displays the count of people within the three categories (gender, ETNY, and travel) within each city. How I achieve this in excel is a trick creating a separate pivot table for each category and simply hiding the repeated columns giving the illusion of one table.

The issue with the matrix table in pbi is that it layers on these different field values rather than keeping them separate. 

Input data:

City	Gender	ETNY	Travel	Person ID
Orlando	Male	BL	Yes	1
Miami	Male	HI	Yes	2
New York	Female	IN	Yes	3
DC	Male	AS	Yes	4
DC	Female	BL	No	5
DC	Female	HI	Yes	6
New York	Female	IN	No	7
Houston	Female	AS	Yes	8
DC	Female	BL	Yes	9
New York	Male	HI	No	10
Houston	Female	IN	No	11
Miami	Female	AS	Yes	12
New York	Male	BL	No	13
Orlando	Female	HI	Yes	14
Orlando	Male	IN	Yes	15
Orlando	Female	AS	No	16

Desired output:

	Gender	ETNY	Travel
City	Female	Male	AS	BL	HI	IN	No	Yes	Total number of Person IDs
DC	3	1	1	2	1		1	3	4
Houston	2		1			1	1	1	2
Miami	1	1	1		1			2	2
New York	2	2		1	1	2	3	1	4
Orlando	2	2	1	1	1	1	1	3	4
Grand Total	10	6	4	4	4	4	6	10	16

[ahadkarimi · Accepted solution]

Hi @mlim0806, give these a try, and if you run into any issues, let me know.

Measures:

Female Count = 
CALCULATE(COUNT('Table'[Person ID]), 'Table'[Gender] = "Female")

Male Count = 
CALCULATE(COUNT('Table'[Person ID]), 'Table'[Gender] = "Male")

AS Count = 
CALCULATE(COUNT('Table'[Person ID]), 'Table'[ETNY] = "AS")

BL Count = 
CALCULATE(COUNT('Table'[Person ID]), 'Table'[ETNY] = "BL")

HI Count = 
CALCULATE(COUNT('Table'[Person ID]), 'Table'[ETNY] = "HI")

IN Count = 
CALCULATE(COUNT('Table'[Person ID]), 'Table'[ETNY] = "IN")

Yes Count = 
CALCULATE(COUNT('Table'[Person ID]), 'Table'[Travel] = "Yes")

No Count = 
CALCULATE(COUNT('Table'[Person ID]), 'Table'[Travel] = "No")

use the measures to create a summary table in a matrix visual:
Drag City to Rows.

Drag Female Count, Male Count, AS Count, BL Count, HI Count, IN Count, No Count, Yes Count, to Values in the matrix.
add another measuer:

Total Persons = COUNT('Table'[Person ID])

[mlim0806]

Easy solution! Thank you!

[ahadkarimi]

My pleasure!

[amitchandak]

@mlim0806 , One of the ways is to unpivot the columns Gender, ETNY, Travel. But that will increase table rows 

Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g