Forum Discussion

mlim0806's avatar
mlim0806
Frequent Visitor
2 years ago
Solved

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 creat...
Need Help
Tips and Tricks
Tutorial Requests
  • ahadkarimi's avatar
    ahadkarimi
    2 years ago

    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])


     

ahadkarimi's avatar
ahadkarimi
Solution Specialist
2 years ago

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's avatar
    mlim0806
    Frequent Visitor
    2 years ago

    Easy solution! Thank you!

    • ahadkarimi's avatar
      ahadkarimi
      Solution Specialist
      2 years ago

      My pleasure!