Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Multiple Unpivot Issues on Charts

Hi All, 

 

I have the two sets of 4 columns that I seperately unpivotted to create 2 charts. The original raw data looks like the following:

 

 

I then unpivot both seperately to create these columns:

 

The problem is doing the 2nd Unpivot inflated the figure in the 1st Unpivot chart (see below):

 

This is the result of 1st Unpivot BEFORE I did 2nd Unpivot.

This is the result AFTER I did 2nd (another) unpivot; The figure in the chart inflated significantly AND the 'Brand attribute' filter doesnt work on the Price Line chart.

 

 

For creation of both charts, I have been enquiring the Power BI community. Links for this issue:

1. Raw Data & Latest Pbix: here 

2. Brand Pie Chart Thread: here

3. Pricing Line Chart thread: here

 

General Comment
Show and Tell

4 Replies

  • danextian's avatar
    danextian
    Super User
    2 years ago

    Hi Anonymous ,

     

    I would suggest you posted your sample result. For example for id 1, how do you want your data to look after the transformations? That way the community will have a better idea of what you're trying to achieve.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

     

    • danextian's avatar
      danextian
      Super User
      2 years ago

      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

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.