Forum Discussion
Multiple columns and values in Matrix
Hello,
I'm trying to achieve the below in Power BI Matrix. My GENDER column has possible 'male' and 'female' values for each office. Next 4 columns, TABLEAU, POWER BI, MS EXCEL and SPSS are 4 different columns of my dataset with numeric values. I can pull multiple columns and values using COUNTIFS in excel, however I am astruggling to achieve this in Power BI. Thank you for providing any help.
Here are the steps:
1. Added an index column to the PowerBI_Ready table.
2. Duplicated the table using 'Reference' option and renamed it PowerBI-Ready_Gender.
3. Unpivoted the PowerBI_Ready_Gender table.
4. Power BI will automatically create the relationship between these two tables using the Index column.
5. Add the below two Measures in the PowerBI_Ready_Gender table.
Male = CALCULATE(COUNT(PowerBI_Ready_Gender[Gender]), PowerBI_Ready_Gender[Gender]="Male", PowerBI_Ready_Gender[Value]>0)
Female = CALCULATE(COUNT(PowerBI_Ready_Gender[Gender]), PowerBI_Ready_Gender[Gender]="Female", PowerBI_Ready_Gender[Value]>0)
The result:
Download the Power BI file here.
7 Replies
Can you detail a bit more what is the result you want to achieve and some sample result?
anandav, Thanks. Please see the below image. I have been able to achieve this in Power BI, pretty easy. I added data from field named SOFTWARE USED and added in the column and values field of the matrix.
I want to add another field from my dataset named GENDER, and achive the results as displayed below (blue rectangle), where in the matrix it should list the number of male and female staff by Office / Region (once only). The problem is when I add the GENDER field in columns / values field of matrix, the number of male / female are being repeated for each SOFTWARE. So I get male/ female for tableau, male / female for Power BI. I only need the MALE / FEMALE numbers to appear once. I hope I was able to make it clear. Thanks.
Here are the steps:
1. Added an index column to the PowerBI_Ready table.
2. Duplicated the table using 'Reference' option and renamed it PowerBI-Ready_Gender.
3. Unpivoted the PowerBI_Ready_Gender table.
4. Power BI will automatically create the relationship between these two tables using the Index column.
5. Add the below two Measures in the PowerBI_Ready_Gender table.
Male = CALCULATE(COUNT(PowerBI_Ready_Gender[Gender]), PowerBI_Ready_Gender[Gender]="Male", PowerBI_Ready_Gender[Value]>0)
Female = CALCULATE(COUNT(PowerBI_Ready_Gender[Gender]), PowerBI_Ready_Gender[Gender]="Female", PowerBI_Ready_Gender[Value]>0)
The result:
Download the Power BI file here.
