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

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