Forum Discussion
Helper IINeed 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 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.
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.
3 Replies
- MFelix
Super User
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. 
v-jingzhangCommunity Support
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.
