Need Advice: How to visualize Ordering towards Forcast

Hello,

I'm new to PowerBI and I want some advice how to tackle this issue. I've got 2 Excels:

Excel 1: Orders Excel - This contains all the orders of 2024

Typical row: Date - Supplier - Order Value - Description - ...

Excel 2: Forcast Excel - This contains the forcast for every supplier (weekbase)

! Every supplier is set up as a column and in the rows are the values per week

Typical row: Weeknumber - Total Forcast - Forcast value Supplier1 - Forcast value Supplier2 - ...

How can I easily visualize if the total ordering from a supplier start deviating on a certain week?

For example: on week 10 we see >20% deviation in order value of supplier 3 in comparison with the forcast value we had for that supplier.

My idea was to:

1) Make a new table "Supplier - Total Order" with a unique list of the suppliers from the Orders Table (copy + remove duplicates)

2) In the new table "Supplier - Total Order", make a new calculated column of SUMIF values  from the Orders Table

-> I'm stuck in this step. Help!

3) "Supplier - Total Order" Table: Convert columns to row => Suppliers are header in new Table

4) Compare "Supplier - Total Order" with table "Forcast" and do some calculates to determine deviation% and visualize.

I can't share my data because it is not in lign with my company policy. My apologies for this.

All advice is very hard appreciated.

Kind regards,

Jelse