Need to create measure

[mehlenbae2]

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 Satisfaction for each Product Type because of conditional formatting (each product type has different customer satisfaction target). However, when I average a product type it looks at the entire table instead of the rows that specify the a particular Target Type. Keep in mind I need all of the Product Type values in the Matrix seperately. I am aware that it works perfectly when you put Product Type as Columns and average it that way however conditional formatting will not work the way I want it.

I would attach my test PBI but am not sure how.. Here is a picture:

Please help. I've been working at this for awhile.

[v-jingzhang · Accepted solution]

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]

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.

[MFelix]

Hi @mehlenbae2 

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.