Forum Discussion

lisago1978's avatar
lisago1978
Helper III
6 years ago
Solved

Columns to Rows in Summary Table

I am trying to take a series of dichotomous colum ns (y/n) and create a summary table where these become rows in either Power BI or Power Query. The table should be dynamic in that the totals will...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi lisago1978 ,

    If you don't want to break the original structure of table, you can create category dimension table first. Then create a measure to get the count of per category. The specific details as follow screen shot:

    1. Create category table

    2. Create a measure with the below formula

    CountofCategory = 
CALCULATE (
    DISTINCTCOUNT ( 'inquiry'[Inquiry] ),
    FILTER (
        ALL ( 'inquiry' ),
        SEARCH ( MAX ( 'Category'[Category] ), 'inquiry'[Category], 1, 0 ) > 0
    )
)

    Best Regards

    Rena

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi lisago1978 ,

You can follow the below steps to achieve your requirement:

1. Unpivot the columns Isolation, Shelter, Food and Mask

Unpivot columnsafter unpivot

2. Create a measure to get the count of status with "yes" per category, then drag the field Attribute and Value onto Table visual

Measure = CALCULATE(COUNT('inquiry'[Value]),FILTER('inquiry','inquiry'[Value]="Yes"))

Best Regards

Rena

lisago1978's avatar
lisago1978
Helper III
6 years ago

If I unpivot the columns in my data table I lose all of the other important fields. Is there a summarize feature that does not change the parameters of the table, each record has a unique ID that I don't want to  lose rather just have a separate table that summarizes these yeses from the columsn and has the columns as rows in a separate summary table.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi lisago1978 ,

    If you don't want to break the original structure of table, you can create category dimension table first. Then create a measure to get the count of per category. The specific details as follow screen shot:

    1. Create category table

    2. Create a measure with the below formula

    CountofCategory = 
CALCULATE (
    DISTINCTCOUNT ( 'inquiry'[Inquiry] ),
    FILTER (
        ALL ( 'inquiry' ),
        SEARCH ( MAX ( 'Category'[Category] ), 'inquiry'[Category], 1, 0 ) > 0
    )
)

    Best Regards

    Rena