cancel
Showing results for
Did you mean:
Frequent Visitor

## Is it possible to do calculations between two charts with different data sources?

I have two separate data sources, 1 spreadsheet with item by item sales data per store.

 StoreID SKU Date Amount 1 91091d46-c523-48d4-9741-61452a150ba3 10/1/2017 1000 1 dadb7f0e-5507-4151-8b05-a0a6f4a6d20c 10/3/2017 2500 1 8ebda452-e0b4-4f73-bbc5-4f5cd5d4a5c9 10/6/2017 5506.73

and another with the forecast by month per store.

 StoreID Month Forecast 1 October 12000 2 October 5000 3 October 20000

I have a chart that shows the sums up the actual sales in a given month and another that shows the forecast. Is there a way to have a chart that shows the difference between the actual and the forecast within Power BI?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Is it possible to do calculations between two charts with different data sources?

Hi,

1. In the Forecast Table, create a dummy date column
2. Create a calendar Table.  Extract Month and Year using the MONTH() and YEAR() functions.
3. Create a relationship from the Date columns in both datasets to the Date column in the Calendar table
4. Create a unique StoreID Table
5. Create a relationship from the StoreID column of both datasets to the StoreID column of the table create in point 4 above
6. Drag StoreID form from the StoreID table and Year/Month from the Calendar Table
7. Write the following measures

Total sales = SUM(Data[Amount])

Forecast sales = SUM(Forecast[Forecast])

Variance = [Total sales]-[Forecast sales]

Hope this helps.

Super User

## Re: Is it possible to do calculations between two charts with different data sources?

Hi,

1. In the Forecast Table, create a dummy date column
2. Create a calendar Table.  Extract Month and Year using the MONTH() and YEAR() functions.
3. Create a relationship from the Date columns in both datasets to the Date column in the Calendar table
4. Create a unique StoreID Table
5. Create a relationship from the StoreID column of both datasets to the StoreID column of the table create in point 4 above
6. Drag StoreID form from the StoreID table and Year/Month from the Calendar Table
7. Write the following measures

Total sales = SUM(Data[Amount])

Forecast sales = SUM(Forecast[Forecast])

Variance = [Total sales]-[Forecast sales]

Hope this helps.

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 115 members 1,593 guests
Recent signins: