Measures to Columns

[adventurer7]

Hello. I currently have a table in this format :

Name	Category A	Category B	Category C
Name 1	0	0	1
Name 2	1	0	1
Name 3	1	1	1
Name 4	1	0	0
Name 5	0	1	0

And I have these measures to count the zeroes :

Category A Count = COUNTROWS(FILTER(Table1,Table1[Category A]=0))

Category B Count = COUNTROWS(FILTER(Table1,Table1[Category B]=0))

Category C Count = COUNTROWS(FILTER(Table1,Table1[Category C]=0))

And the percentage of zeroes over count of rows :

Category A % = Category A Count/COUNT(Table1[Name])

Category B % = Category B Count/COUNT(Table1[Name])

Category C % = Category C Count/COUNT(Table1[Name])

Is there a way to achieve below matrix format without unpivoting the table? I think there should only be one measure for count and one for % but I can't make it work. Thanks in advance!

	Count	%
Category A	2	40%
Category B	3	60%
Category C	2	40%

[v-yueyunzh-msft · Accepted solution]

Hi, @adventurer7 

Here are the steps you can refer to :
(1)My test data is the same as yours.

(2)We can use dax to unpivot your table, you can click "New Table":

Table 2 = UNION( ADDCOLUMNS( SELECTCOLUMNS('Table',"Value",[Category A],"Name",[Name]) , "Category","Category A"),
ADDCOLUMNS( SELECTCOLUMNS('Table',"Value",[Category B],"Name",[Name]) , "Category","Category B"),
ADDCOLUMNS( SELECTCOLUMNS('Table',"Value",[Category C],"Name",[Name]) , "Category","Category C"))

(3)Then we can create two measures:

Count = CALCULATE( COUNT('Table 2'[Value]) , 'Table 2'[Value]=0)

% = DIVIDE([Count], DISTINCTCOUNT('Table 2'[Name]))

(4)Then we can put the measure on the visual and we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[adventurer7]

Works like a charm! Thanks Aniyaaaa appeciate it

[bolfri]

Can you tell us why you don't want to unpivot the data?

[adventurer7]

Hi! There are already existing visuals and several columns/measures made using this table format.

Are you suggesting I reference/duplicate the table and unpivot it?

[bolfri]

Actually sometimes it's better to rebuild whole dashboard using correct model than trying to find a "workaround" solutions that you will hate in the future. Power BI is super-easy, fast and even if you need to rebuild some stuff it will be much easier, because right know you know what you want.

If you want, you can send me a pbix file. I can rebuild it for you. 🙂

[amitchandak]

@adventurer7 , In the Matrix switch values to row, should do the job

of try calculation group

Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0