Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Slin
New Member

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?

 

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

1 REPLY 1
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.