Multiple Unpivot Issues on Charts

Hi danextian 

I am a total newbie to Power BI so I am not even sure what final data structure should look like for my charts and filter to work.

I have transformed my table from BEFORE to AFTER (but the AFTER structure failed to delivered my goal charts in section 2 as some issues in section 3 arised).

So I am looking for advice why it didnt fully work for my charts (the issues mentioned in section 3 above); whether it need further processing or entirely need to be structured differently.

BEFORE (RAW DATA)

AFTER (CURRENT STATE)

Processing done so far:

- Unpivot Blue and Pink  to brand_attribute and brand_value (1st Pivot)

- Unpivot Yellow and Green to Price_attribute and Price_Value (2nd Pivot)

POTENTIAL ISSUES WITH DOING 2 SEPERATE UNPIVOT PROCESSES

I have attached the checking excel data from RAW TO 1st unpivot TO 2nd unpivot here

The file name is Raw Data Transformation.xlsx

1. Repeated Brand attribute after 2nd unpivot

In the file, I can see that as a result of 2nd Unpivot, the Likelihood_honda for case id 1 has been repeated twice. This might be why my pie chart figure increased significantly.

Should I group by the brand_attribite field or what do you reckon should be the appropriate solution?

2. Brand_attribute slicer didnt work on line chart

Theoritically, by selecting the brand_attribute slicer, the figure in the pricing line chat should changed but that is not the case.

Is it because the brand_x fields  and price_X fields are seperated as a result from 2 different unpivot processes? If so, how should we structure the data so we can filter price chart using brand_attribute slicer? Should I unpivot all the column together as a unit? 

Overall, how do you think my 2nd unpivot result should look like for my chart to work properly in Power BI? and how can I achieve so using DAX or Power BI processing?

Thank you in advance for your patience and help 🙂. It would be helpful and appreciated if anyone can provide me with a working pbix and some instruction how to do it.