Forum Discussion

mehlenbae2's avatar
mehlenbae2
Icon for Helper II rankHelper II
3 years ago
Solved

Need to create measure

I am trying to replicate this Excel Pivot Table in PBI by using a matrix:     Here is my table:     My issue is that I need to create a measure to calculate average Customer Satisfa...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    Hi mehlenbae2 

     

    You need to transform the table with Power Query Editor first. This will make it easy to achieve your expected result. Please follow these steps:

     

    1. Select four "Product Type X" columns and unpivot them. You will have a new "Attribute" column and a "Value" column. 

     

    2. Filter rows by "Attribute" column via Ends With. You can enter any value here e.g. "A". We will modify this value later. 

     

    3. In formula bar, replace "A" with [Product Type]. This is to filter rows whose "Product Type" is identical with the product type value in "Attribute".

     

    4. Now the table is friendly to be used in a matrix. Apply this change to Power BI Desktop. Then create a matrix with below fields. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

v-jingzhang's avatar
v-jingzhang
Icon for Community Support rankCommunity Support
3 years ago

Hi mehlenbae2 

 

You need to transform the table with Power Query Editor first. This will make it easy to achieve your expected result. Please follow these steps:

 

1. Select four "Product Type X" columns and unpivot them. You will have a new "Attribute" column and a "Value" column. 

 

2. Filter rows by "Attribute" column via Ends With. You can enter any value here e.g. "A". We will modify this value later. 

 

3. In formula bar, replace "A" with [Product Type]. This is to filter rows whose "Product Type" is identical with the product type value in "Attribute".

 

4. Now the table is friendly to be used in a matrix. Apply this change to Power BI Desktop. Then create a matrix with below fields. 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi mehlenbae2 

     

    Update:

     

    I found another solution that you don't need to transform the table. You can use the "Customer Satisfaction Met" column for average value directly.

     

    I have attached a pbix file with both samples.

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.