Forum Discussion
Multiple Unpivot Issues on Charts
Hi danextian
Here is the Raw Data & Latest Pbix: here
1. The End Goal
The end goal is to create a Likelihood pie chart and Pricing (Cheapest to Most Expensive) Line chart.
In the pbix, I have managed to achieve this (but there are some issue with number/figure shown) in the charts. I will explain in the section 3 below.
2. My approach to the goal
BEFORE PROCESSING
This is how the raw data looks like:
Likelihood_ columns will be used to create 'likelihood by brand' pie chart and Brand_Attribute filter
Price_ columns willl be used to create the pricing line chart.
AFTER PROCESSING
I have done the following: to the data:
1a. Unpivot Likelihood_X columns
1b. Create likelihood pie chart and brand attribute filter
2a. Do another unpivot on Price_X columns
2b. Create Price Line chart
3. Issues to Section 2 Approach result
However, there are some issues with my approach above:
1. The figure in pie chart after the 2nd Unpivot (after step 2a and 2b are done) has increased significantly and not accurate.
BEFORE
AFTER
As shown above, the extremely likely figure jump from 156 to 396 and somewhat likely has jumped from 86 to 178.
2. The brand filter doesnt work on the line chart. The figure stays the same.
I have previously asked help for each chart creation on Power BI community.
a. Brand Pie Chart Thread: here
b. Pricing Line Chart thread: here
Please let me know if anything need to be clarified.
Thank you in advance
Hi Anonymous ,
My apologies if I didn't make myself clear. The charts aside, if you were to manually transform your data (id 1) in Excel how would it look after doing so? Which cell goes to which column/row? Please see an example below
Before
After
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.
