Add column to table from another table where there is a many to many relationship

[monktrain]

I have a Sales Order Lines table and a Forecast table.  There is a Part Category column in the Sales Order Lines table that I need in the Forecast table so I can show a forecast by Part Category.  There is a many-to-many relationship with the part numbers because the same part number may be on many sales order lines, and the part number is also forecast for several different months.  I have tried various versions of LookupValue, NATURALLEFTOUTERJOIN and FILTER and I just can't seem to get there.  Can anyone help?  Here is a basic layout of my tables:

I have also tried to calculate the sum of the forecasted amount (no shown) in a measure, with no success. This is my measure for that:

Category Monthly Goal = sumx(filter(Forecast,Forecast[ForecastStartDate]=date(2020,3,1) && MAX(SalesOrderLines[Category]) = "Wheel"),[ForecastedAmount])

That does not throw up an error, but I do not get a value.

Thanks,

David

[v-juanli-msft]

Hi @monktrain 

What is your measure [ForecastedAmount]?

Which tables does the measure use?

Best Regards

Maggie

[monktrain]

Sorry, the [ForecastedAmount] is actually in the Forecast table.  I forgot to put that in there. 

Thank you.

[v-juanli-msft]

Hi @monktrain 

Add [amount] from "forecast table" to the table visual, it would sum the [amount] based on the column [Category].

What's wrong?

What is the expected result do you like?

Best Regards

Maggie

[Mariusz]

Hi @monktrain 

You can use a budget pattern for your scenario, as per below.
https://www.daxpatterns.com/budget-patterns/

Also, it's considered as a best practice to avoid many to many, so you can create Product Table and join both tables on 1:*

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[monktrain]

Thank you.  I will check this out.  

I would love to avoid the many-to-many relationship.  Unfotunately, this information is coming from an ERP system where I cannot change the data entities.