We’ll need to display the total assets in each portfolio. for that first we’ll need for each movement to have two Power FX fields: one showing the amount of assets and the other displaying the amount of currency for that movement. This setup will allow us to aggregate them in the future. The necessary data is already available—we just need to add the appropriate symbol (+ or -).
Now, let’s create the asset quantity amount:
Switch('Movement type',
'Movement type (Movements)'.Adjustment, Quantity,
'Movement type (Movements)'.Buy, Quantity,
'Movement type (Movements)'.Sell, -Quantity,
0.0)
If the movement type is Adjustment or Buy, we keep the symbol entered by the user. If it’s Sell, we make it negative. For all other cases, the value is set to 0.
Let’s create the currency amount
Switch('Movement type',
'Movement type (Movements)'.Adjustment, Decimal(Amount),
'Movement type (Movements)'.'Cash deposit', Decimal(Amount),
'Movement type (Movements)'.'Cash withdraw', -Decimal(Amount),
'Movement type (Movements)'.Buy, -Decimal(Amount),
'Movement type (Movements)'.Sell, Decimal(Amount),
'Movement type (Movements)'.Dividend, Decimal(Amount),
'Movement type (Movements)'.Expense, -Decimal(Amount),
0.0)
Similarly, for the total amount of currency, we follow the same logic. However, in this case, we also need to convert the currency to a decimal format.
Let’s review some of the movements to ensure they are correct:
As we can see, all movements have a currency amount, but not all require a quantity. Therefore, we can simply hide this field when the movement type is not Buy, Sell, or Adjustment, using a business rule similar to the one we applied in previous series:
Now save and activate.
In the upcoming posts, we’ll create an entity related to the Portfolio table, with a record for each asset type on that portfolio and its total amount. This total will be calculated based on an aggregation of the fields we’ve created.
Leave a Reply