I have a table with many different transactions (share transactions) and I want to summarize the investment in a pivot table based on Asset name and Portfolio.

Somehow I am able to sum the amount of shares per asset per portfolio useing a formula field (footer: sum)with: number(Amount).

In order to calculate the Investment I'm using the following code in a formula field with footer: sum:

let i := sum((select Transactions)[Type like "Buy"].'Total Value');
let j := sum((select Transactions)[Type like "Sell"].'Total Value');
let k := number(j) + number(j);
k

It does not correctly sum the bought and sold shares of that specific Asset in that specific Portfolio. 

I'm out of options and would really appreciate your help!

Best regards,

Imar