Calculating value based on columns in two different tables

[Dr_Chris]

Hi - Newbie question...  
I've searched hard for a solution to this question and have tried several different ideas but keep getting an error message or values that do not make sense.

I have two tables:

CustomerOrders

which contains several columns including ProductCode and QtyOrdered

and

WarehouseStock

which contains columns including ProductCode, StockQty and Warehouse

Packaged items ready for dispatch are in Warehouse 1

There is a Many-to-Many, two way relationshp on the ProductCode

To help production, I have created a report with a Matrix visual which shows the ProductCode in the rows, and then two columns - the QtyOrdered and the StockQty.  The data is filtered on Warehouse = 1
This works fine

Now I want to display the shortfall in a third column.

Even a simple attempt to create a new column in the WarehouseStock using the Related DAX function 

Shortfall= related(CustomerOrders[QtyOrdered])

  fails with the error

The column 'CustomerOrders[QtyOrdered]' either doesn't exist or doesn't have a relationship to any table available in the current context.

What am I doing wrong?

Thanks in advance !

[Anonymous]

Hi everyone, 

I need help with solving one problem. 

Take a look at this demo table: 

We have 4 different columns, one is "client", the second one is "transporter". Some of our clients are also and transporter. 
I want to make a mutual column and summarize money we earn cooperating with that client ( sum of SUM EUR) - Looking column "client" and "SUM EUR" column, and looking the "Transport" column and the column "price to transporter". 

I want to see how much money we give to transporters and how much they give us as a client we work for. 

For example, "client 8" as a client gives us 420€, but we give "client 8" as a transporter 300€. 

I just want to compare these two values with mutual clients I have in column "client" and column "Transporter". 

Thanks in advance. 

[lbendlin]

"There is a Many-to-Many, two way relationshp on the ProductCode" 

Please explain the rationale behind this decision.  Please show your data model.

[Dr_Chris]

Here is an example of the data in the two tables

CustomerOrders

Date	Customer Ref	PurchaseOrderRef	ProductCode	QtyOrdered
01/01/2020	C-0091	PO-1234	A1	10
01/01/2020	C-0871	PO-9821	B1	20
02/01/2020	C-0020	PO-7212	A1	5
02/01/2020	C-0020	PO-7212	C1	20
02/01/2020	C-9817	PO-A0912	A1	12

From this I can calculate that I need A1 - 27 units, B1 - 20 units etc
Of course there is much more data in the table, so I am interested in the number of units required this week, 

and WarehouseStock

Warehouse	Location	ProductCode	StockQty
1	A1	A1	100
1	C1	B1	12
2	R	A1	50
1	Q	A1	60

etc.  I can calculate that I have 100 

This structure is inherited from a third party package which we connect to via SQL so I have no control over the structure

[lbendlin]

Please show a picture of your Power BI data model. Third icon in the left bar.

[Dr_Chris]

Here it is.   Note I've considerably simplified the description in explaining the issue above.

[lbendlin · Accepted solution]

You would propose that you extract the product/item object into its own dimension table, which then feeds the inventory and backorder fact tables with a unique address of 1:* each.