Forum Discussion
Create one table with split columns for each field values
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 |
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])
4 Replies
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
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])
Easy solution! Thank you!
My pleasure!
