Forum Discussion
Measures to Columns
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% |
Hi, Anonymous
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
6 Replies
Anonymous , 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
Can you tell us why you don't want to unpivot the data?
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. 🙂
Hi, Anonymous
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
