Forum Discussion
Columns to Rows in Summary Table
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
| Inquiry | Category | Isolation | Shelter | Food | Mask |
| 1 | Isolation, Food | yes | no | yes | no |
| 2 | Isolation | yes | no | no | no |
| 3 | Shelter, Food, Mask | no | yes | yes | yes |
| 4 | Isolation, Shelter, Mask | yes | yes | no | yes |
| 5 | Food, Mask | no | no | yes | yes |
| 6 | Isolation, Shelter, Mask | yes | yes | no | no |
| 7 | Shelter, Food | no | yes | yes | no |
| 8 | Isolation Mask | yes | no | no | yes |
| 9 | Mask | no | no | no | yes |
That is the data table and this is the calculated summary table that I want in Power Query
| Category | Count |
| Isolation | 5 |
| Shelter | 4 |
| Food | 4 |
| Mask | 5 |
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
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.
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