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

[NetromEner]

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.

[tamerj1]

@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]
)

[NetromEner]

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

[tamerj1 · Accepted solution]

@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]
)