Reply
Frequent Visitor
Posts: 2
Registered: ‎11-08-2017
Accepted Solution

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.

StoreIDSKUDateAmount
191091d46-c523-48d4-9741-61452a150ba310/1/20171000
1dadb7f0e-5507-4151-8b05-a0a6f4a6d20c10/3/20172500
18ebda452-e0b4-4f73-bbc5-4f5cd5d4a5c910/6/20175506.73

 

and another with the forecast by month per store.

StoreIDMonthForecast
1October12000
2October5000
3October20000

 

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?

 

 


Accepted Solutions
Super User
Posts: 3,943
Registered: ‎01-14-2017

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.

View solution in original post


All Replies
Super User
Posts: 3,943
Registered: ‎01-14-2017

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.