How to subtract columns from 2 different table based on buy/sell condition

[camelia_]

hii~

I need help in finding this relative price( to find profit/loss),where i need to subtract Traded Price(from Sample Transactions table) with Market Price(from Price table), based on the columns of buy/sell (from Sample Transactions table).

Here is what i did, but it can't be calculated and I can't put it in the matrix table.

Column = IF('Sample Transactions'[Buy/Sell]= "Sell",'Sample Transactions'[Total Trade Price*]-SUM('Price'[PRICE]), IF('Sample Transactions'[Buy/Sell]="Buy", -1*('Sample Transactions'[Total Trade Price*]-SUM('Price'[PRICE])),""))

Also, i did this one,

Measure = IF(ALL('Sample Transactions'[Buy/Sell])= "Sell", SUM('Sample Transactions'[Total Trade Price*])-SUM('Price'[PRICE] ), IF(ALL('Sample Transactions'[Buy/Sell])="Buy", -1*(SUM('Sample Transactions'[Total Trade Price*])-SUM('Price'[PRICE])),""))

But also can't be calculated. Hope someone can help me solve this problem.

Thanks in advance.

[YalanWu_test · Accepted solution]

Hi, @camelia_ ;

Try it.

Measure =
IF (
    MAX ( 'Sample Transactions'[Buy/Sell] ) = "Sell",
    SUM ( 'Sample Transactions'[Total Trade Price*] ) - SUM ( 'Price'[PRICE] ),
    IF (
        MAX ( 'Sample Transactions'[Buy/Sell] ) = "Buy",
        -1
            * ( SUM ( 'Sample Transactions'[Total Trade Price*] ) - SUM ( 'Price'[PRICE] ) ),
        BLANK ()
    )
)

or

Column =
IF (
    'Sample Transactions'[Buy/Sell] = "Sell",
    'Sample Transactions'[Total Trade Price*] - SUM ( 'Price'[PRICE] ),
    IF (
        'Sample Transactions'[Buy/Sell] = "Buy",
        -1
            * ( 'Sample Transactions'[Total Trade Price*] - SUM ( 'Price'[PRICE] ) ),
        BLANK ()
    )
)

Best Regards,

[camelia_]

hii @YalanWu_test 

If i have this function,

Gain/Loss = IF (

MAX ( 'Sample Transactions'[Buy/Sell] ) = "Sell",

(SUM ( 'Sample Transactions'[Total Trade Price*] ) - SUM ( 'Price'[PRICE] )) / SUM ( 'Price'[PRICE] ),

IF (

MAX ( 'Sample Transactions'[Buy/Sell] ) = "Buy",

(-1*( SUM ( 'Sample Transactions'[Total Trade Price*] ) - SUM ( 'Price'[PRICE] )) / SUM ( 'Price'[PRICE] )),

BLANK ()

)

)

how can i seperate it to put in 2 cards visualisation as Gain and Loss?

because i try to do seperate measure,

Gain = IF('Sample Transactions'[Gain/Loss] > 0, 'Sample Transactions'[Gain/Loss] , BLANK())

Loss = IF('Sample Transactions'[Gain/Loss] <= 0, 'Sample Transactions'[Gain/Loss] , BLANK())

but it appears blank the card

[camelia_]

omg it worked,thanks a lot!

[amitchandak]

@camelia_ , You need to create a common column and then you can take diff of measures from two tables against common dimensions 

https://amitchandak.medium.com/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solutio...

[camelia_]

Hi @amitchandak , I already have the combined table like in attached link, its just that i can't subtract it since it involves 2 different tables with condition.