Reference a Column in a Table Variable in Function

Question

I'm Trying to learn PowerBI by designing a Stock Portfolio Analysis App.

Here's the sample data of Table - Tradebook_1

Symbol	ISIN	Trade Date	Exchange	Segment	Series	Trade Type	Auction	Quantity	Price	Trade ID	Order ID	Order Execution Time	CurrentPrice
IRFC	INE053F01010	04-09-2023	NSE	EQ	EQ	buy	FALSE	15	65.6	23012505	1.1E+15	04-09-2023 10.57	142.3
IRFC	INE053F01010	13-09-2023	BSE	EQ	A	sell	FALSE	15	78.32	182294200	1.6946E+18	13-09-2023 15.17	142.3
IRFC	INE053F01010	19-12-2023	NSE	EQ	EQ	buy	FALSE	100	95.15	20190250	1.1E+15	19-12-2023 9.16	142.3
IRFC	INE053F01010	19-12-2023	NSE	EQ	EQ	sell	FALSE	100	95.75	20228314	1.1E+15	19-12-2023 9.16	142.3

I'm trying to create a measure which calculates current Portfolio value on the basis of Above Table, Tradebook_1 by adding stocks with Tradebook_1 = "buy" and subtracting Tradebook_1 = "sell" and then multiplying it with Tradebook_1[CurrentPrice]

Hence, the total quantity of the above stock should be 0 in portfolio value.

Here's the measure -

//Current Portfolio Value
//Current Distinct Stocks in Tradebook irrespective of they're present in Portfolio or not.
VAR CurrentStock = VALUES(Tradebook_1[Symbol])

//Current Stock
//Calculate Quantity of Stocks in Portfolio, Add "buy", Subtract "sell
Var AllStockQ = 
SUMMARIZE(Tradebook_1, Tradebook_1[Symbol],"CurrQuantity", SUMX(Tradebook_1, IF(Tradebook_1[Trade Type] = "buy", Tradebook_1[Quantity], - Tradebook_1[Quantity])))
// Using Filter to reference column in Table Var AllStockQ Joining to Tradebook_1 to get CurrPrice
VAR CurrStockQ = 
NATURALINNERJOIN( FILTER(AllStockQ, [CurrQuantity] > 0), Tradebook_1)
//Get Current Total Portfolio Value
VAR PortfolioVal = SUMX( CurrStockQ , [CurrentPrice] * [CurrQuantity] )

RETURN CurrStockQ

I'm getting an error for the same. How does one go about referencing a column in a table assigned to a variable? I googled it and found CALCULATETABLE won't work but Filter will. Need some help with this measure.

Edit - I'm trying to use DAX Queryview, my variable CurrStockQ is returning a table joined with Tradebook_1. I just want to use this variable to calculate the Portfolio Value.

PBIX File - Here

Answer 1

Is it not just this? Should the buy price not be the Price column?

Measure = 
VAR b = CALCULATE(SUMX(Tradebook_1, Tradebook_1[Quantity] * Tradebook_1[CurrentPrice]), Tradebook_1[Trade Type] = "buy")
VAR s = CALCULATE(SUMX(Tradebook_1, Tradebook_1[Quantity] * Tradebook_1[CurrentPrice]), Tradebook_1[Trade Type] = "sell")

RETURN b-s