Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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.
Hi @Malick2018,
Can you please share a dummy sample of the original data?
Best Regards,
Dale
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.
Thank for the method. For Statuc values like Gender, this method is OK.
What if we need to get the dynamic column and its values in Matrix?
Ex:
(Static Columns)
Gender: Male and Female
Software: SSRS, PowerBI, Tableau
(DYnamic COlumns)
Software: SSRS, PowerBI, Tableau , n......
Clients: IBM, TCS, Infosys, CTS, n.....
User | Count |
---|---|
97 | |
87 | |
78 | |
74 | |
70 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |