Forum Discussion

Malick2018's avatar
Malick2018
Frequent Visitor
8 years ago
Solved

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 dif...
  • anandav's avatar
    anandav
    8 years ago

    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.

     

Malick2018's avatar
Malick2018
Frequent Visitor
8 years ago

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's avatar
anandav
Skilled Sharer
8 years ago

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.

 

  • Malick2018's avatar
    Malick2018
    Frequent Visitor
    8 years ago

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

  • smibrahim's avatar
    smibrahim
    Frequent Visitor
    7 years ago

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