Forum Discussion

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

Helper III

6 years ago

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