Forum Discussion

Mart1980's avatar
Mart1980
Icon for Helper I rankHelper I
3 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 havin...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 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.

     

Mart1980's avatar
Mart1980
Icon for Helper I rankHelper I
3 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
Icon for Super User rankSuper User
3 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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 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.