visualisation

[Mart1980]

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? 

[TomMartens]

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]

Hello Tom,

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

achieved
Website	Jan-19	Feb-19	Mar-19	Apr-19	May-19
x	12	10	13	16	20
y	13	11	14	18	21
z	14	12	15	19	22
Uplift
Website	Jan-19	Feb-19	Mar-19	Apr-19	May-19
x	2	5	8	1	2
y	3	6	9	9	3
z	4	7	10	6	5
Forecast
Website	Jan-19	Feb-19	Mar-19	Apr-19	May-19
x	14	15	21	17	22
y	16	17	23	27	24
z	18	19	25	25	27

thank you in advance

[TomMartens]

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

[Mart1980]

Hello,

Here is the link. https://docs.google.com/spreadsheets/d/1p9ztpr6kNlLW6mxGMSPn7zGq8WBdtphP/edit?usp=sharing&ouid=10936...

all data that i need is on one page 😄

best,

Marta

[Anonymous · Accepted solution]

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.