Let's say we have a population of X securities that are constantly added on top of/removed from. But each time there is a transaction we need to calculate the average cost per unit. I'm including below the sample data with formulas to show the calculation that is needed, but I'll also explain it here:
1. If we're on the first Purchase ever of a specific security, Average cost per unit = Purchase/Sale Price
2. If [description] = "Closing Balance" or [description] = "Disposal" then [Row-1:Average cost per unit]
3. If [description] = "Opening Balance" then [Row-1:MV Per Unit]
4. If [description] = "Purchase" then [Quantity]/[Cumulative Quantity]*[Purchase/Sale Price (Local currency)]+
[Row-1:Cumulative Quantity]/[Cumulative Quantity]*[Row-1:Average cost per unit (local currency)]
Seems like everything is figured out then, but here's the tricky part that I'm having some trouble with. Because Disposal AVGUnitCosts require info from the previous row, but So does Closing Balance, and so does Purchase, it's somewhat of a circular reference. In other words sure you can initialize the Average cost per unit value for Descriptions=Disposal or Closing Balance fairly easily, but the next time you find another one of those descriptions it will be stuck with 0. You will need more formula tools performing the same "initialization". Which means in a scenario where we have 100 purchases + disposals non stop for the same ID, you would have to copy paste those tools 100 times which is obviously not ideal. So I'm missing some part of the solution here but not sure what.
The formulas I explained written are also included in the sample excel. Feel free to use that as input data but the highlighted column should be zero'd out first so that alteryx does the calculation from scratch.
Would appreciate some assistance on this.
