How to replace values based on another cell of another column

[Harshc374]

I have the below data with trade, stock and price column where the output price column should be 

If Trade= "BUY" then Price corresponding to BUY

Else if Trade= "SELL" then Price corresponding to BUY in all sell rows

So basically the output for all the trades should be the Price for BUY trade i.e. 2000 in our example

 

[talespin · Accepted solution]

hi @Harshc374 

If you need it as a Calculated Column. In case there are multiple BUY, it will take MAX of that price, you can modify as per your requirement.

Output =

VAR _Stock = Stock[Stock]

VAR _BuyPrice = CALCULATE(MAX(Stock[Price]), REMOVEFILTERS(Stock), Stock[Stock] = _Stock && Stock[TRADE] = "BUY")

RETURN _BuyPrice

[Anonymous]

Hi @Harshc374 

For your question, here is the method I provided:

Here's some dummy data

"Table"

Create a measure. Query the Price of Trade="BUY", and when Trade="SELL", output the Price of BUY.

Output Price = 
    VAR BUY_Price = 
        CALCULATE(
            VALUES('Table'[Price]), 
            FILTER(
                ALL('Table'), 
                'Table'[Stock] = MAX('Table'[Stock]) 
                && 
                'Table'[Trade] = "BUY"
            )
        )
RETURN 
    IF(
        SELECTEDVALUE('Table'[Trade]) = "BUY",
         VALUES('Table'[Price]),
        BUY_Price
    )

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.