Forum Discussion

Mart1980's avatar
Mart1980
Helper I
2 years ago
Solved

visualisation

Hello guys, 

 

I have the following and what i am trying to replicate in power bi is that graph. so I want to take the achieved by month and compare to the forecast. but what the problem i am having is that if i unpivot the month columns then the forecast values are not separated from the achieved values. And i cannot change the format of this excel file as this is how it is currently set up to be worked on.Any help please? 

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Mart1980 ,

     

    I suggest you to transform your tables by UNPIVOT function in Power Query Editor.

    Your table will look like as below.

    Then create a DimDate to relate two tables.

    Result is as below.

    Uplift Table:

    Uplift = 
SUMMARIZE (
    ALL ( Achieved ),
    Achieved[Website],
    Achieved[Date],
    "Uplift",
        CALCULATE (
            SUM ( Forecast[Value] ),
            FILTER (
                Forecast,
                Forecast[Website] = EARLIER ( [Website] )
                    && Forecast[Date] = EARLIER ( [Date] )
            )
        )
            - CALCULATE ( SUM ( Achieved[Value] ) )
)

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

5 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    2 years ago

    Hey Mart1980 ,

     

    provide an Excel file that contains sample data. Then, it will be possible to provide you with a pbix that creates a table that looks like this:
    datatype | website | date | value

    Make sure that the Excel file represents the structure of your original 100%.

     

    Regards,

    Tom

    • Mart1980's avatar
      Mart1980
      Helper I
      2 years ago

      Hello Tom,

       

      thank you for your quick reply. Please find below as it is not allowing me to add excel files here.

      achieved     
      WebsiteJan-19Feb-19Mar-19Apr-19May-19
      x1210131620
      y1311141821
      z1412151922
            
      Uplift     
      WebsiteJan-19Feb-19Mar-19Apr-19May-19
      x25812
      y36993
      z471065
            
      Forecast     
      WebsiteJan-19Feb-19Mar-19Apr-19May-19
      x1415211722
      y1617232724
      z1819252527

       

      thank you in advance

      • TomMartens's avatar
        TomMartens
        Super User
        2 years ago

        Hey Mart1980 ,

         

        please create an Excel, that exactly represents the structure of the original data, as well as the distribution across multiple pages in the same file if not all data is available from a single sheet.
        Upload the pbix to onedrive, Google Drive, or dropbox and share the link. Make sure that no login is required to download the file.

         

        Regards,

        Tom