Multiple columns and values in Matrix

[Malick2018]

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. 

[v-jiascu-msft]

Hi @Malick2018,

Can you please share a dummy sample of the original data?

Best Regards,

Dale

[Malick2018]

Hi @v-jiascu-msft, Here is the sample dataset. Thanks.

Sample data

[anandav]

@Malick2018,

Can you detail a bit more what is the result you want to achieve and some sample result?

[Malick2018]

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

[anandav · Accepted solution]

@Malick2018,

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.

[smibrahim]

anandav,

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

[Malick2018]

@anandav, Thank you, it works. Your help is appreciated.