Calculate average cost per unit for multiple items

[AkisM]

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. 

[danilang]

Hi @AkisM 

I might be missing something, but if the only column you need to calculate is [Average cost per unit (Local currency)], you can do this in a single Multirow Formula tool with the following formula

if [Description] = "Opening Balance" and  isnull([Row-1:ID]) then
   0
elseif [Description] = "Opening Balance" then
   [Row-1:MV Per Unit]
elseif [Description]="Purchase" and [Row-1:AvgCostPerUnit] =  0 then
   [Purchase/Sale Price (Local Currency)]
elseif [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)])
else // [Description] in ("Closing Balance","Disposal") then
    [Row-1:AvgCostPerUnit]
endif

In the attached workflow I created a new column called AvgCostPerUnit to make it easier to compare with the existing one.

Dan

[atcodedog05]

Hi @danilang 

Just a doubt how are you checking the first condition 🤔

[danilang]

@atcodedog05 

The first condition is caught by the italicized line 

if [Description] = "Opening Balance" and isnull([Row-1:ID]) then
   0
elseif [Description] = "Opening Balance" then
   [Row-1:MV Per Unit]
elseif [Description]="Purchase" and [Row-1:AvgCostPerUnit] = 0 then
   [Purchase/Sale Price (Local Currency)]
elseif [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)])
else // [Description] in ("Closing Balance","Disposal") then
   [Row-1:AvgCostPerUnit]
endif

Dan

[atcodedog05]

Got it, Thanks for the clarification @danilang 🙂👍

[AkisM]

Hi @danilang, sorry for the late reply and thanks for your time. The point that you're missing (from what I understand after checking the workflow you provided) is that you used the AVG cost per unit column in the formula without first zero'ing it. I entered the amounts manually in the "sample in" for the columns that we're trying to calculate just to show what they should look like, including their excel formulas. But in reality, that sample_in data needs to be zeroed out first because the "avg cost per unit column" has not been yet calculated and it's what we're trying to calculate.

[danilang · Accepted solution]

Hi @AkisM 

Sorry about that.  I made a mistake when building the original formula.  The [Row-1:Average cost per unit (Local currency)] reference in the formula should be replaced by [Row-1:AvgCostPerUnit].  

if [Description] = "Opening Balance" and  isnull([Row-1:ID]) then
   0
elseif [Description] = "Opening Balance" then
   [Row-1:MV Per Unit]
elseif [Description]="Purchase" and [Row-1:AvgCostPerUnit] =  0 then
   [Purchase/Sale Price (Local Currency)]
elseif [Description] = "Purchase" then
   ([Quantity]/[Cumulative Quantity]*[Purchase/Sale Price (Local Currency)])+([Row-1:Cumulative Quantity]/[Cumulative Quantity]*[Row-1:AvgCostPerUnit])
else // [Description] in ("Closing Balance","Disposal") then
    [Row-1:AvgCostPerUnit]
endif

New workflow corrects the error.  I also added a formula that 0's out the original column

Dan

[AkisM]

You're completely right @danilang and that's a much simpler solution than what I thought would be required. Not sure how I missed it. Thanks for your time!