topic Lookup cost price based on 4 column values matching in another row in DAX Commands and Tips

Lookup cost price based on 4 column values matching in another row

NetromEner — Mon, 03 Apr 2023 08:52:49 GMT

Hi,

I need some help on how to write a measure to find a price for a line item in a table containing all transactions, including both Sales orders and purchase orders.

The link between the Sale Order Price and purchase Order price is 4 values listed on the sale order line and what I want to find is the Purchase Order price to calculate profit.

The link between Sales order price and Purchase order price is Supplier PO+Item+Product Number+QTY.

Transaction	Product Number	Item	Supplier PO	QTY	Price	Type
539069	8	1	539075	45	100	Sales Order
539075	8	1	0	45	80	Purchase Order

So in the above example I would need to find Price 80 as this match the link Supplier PO+Item+Product Number+QTY

Appreciate some help with this.

Re: Lookup cost price based on 4 column values matching in another row

tamerj1 — Mon, 03 Apr 2023 11:31:50 GMT

NetromEner
Supplier PO is not matching. However, assuming this is just a typo mistake, please try

Cost Price = MAXX ( FILTER ( CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Supplier PO], 'Table'[Item], 'Table'[Product Number], 'Table'[QTY] ) ), 'Table'[Type] = "Purchase Order" ), 'Table'[Price] )

Re: Lookup cost price based on 4 column values matching in another row

NetromEner — Mon, 03 Apr 2023 11:42:34 GMT

Hi tamerj1 and thanks for your feedback. You see the problem is that First line refers to Supplier PO 539075, which again is listed as a separate line with 539075 as transaction ID. So I want to find with price where Supplier PO+Item+Product Number+QTY = Transaction+Item+Product Number+QTY

Hope this explains the problem I am facing, I have to swicth Coloumn when looking up the price for connected Purchase Order

Re: Lookup cost price based on 4 column values matching in another row

tamerj1 — Mon, 03 Apr 2023 12:05:42 GMT

NetromEner

Please try

Cost Price =
MAXX (
FILTER (
CALCULATETABLE (
'Table',
ALLEXCEPT ( 'Table', 'Table'[Item], 'Table'[Product Number], 'Table'[QTY] )
),
'Table'[Transaction] = EARLIER ( 'Table'[Supplier PO] )
&& 'Table'[Type] = "Purchase Order"
),
'Table'[Price]
)