topic Re: How to subtract columns from 2 different table based on buy/sell condition in DAX Commands and Tips

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

camelia_ — Fri, 05 Aug 2022 06:05:49 GMT

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.

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

amitchandak — Fri, 05 Aug 2022 07:09:37 GMT

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-solution-8e3eccb41bda

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

camelia_ — Fri, 05 Aug 2022 08:13:53 GMT

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.

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

YalanWu_test — Tue, 09 Aug 2022 07:06:55 GMT

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 () ) )

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,

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

camelia_ — Tue, 09 Aug 2022 07:14:13 GMT

omg it worked,thanks a lot!

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

camelia_ — Tue, 09 Aug 2022 08:53:21 GMT

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