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.

     

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.