Calculating Weighted Average to determine cost of item sold

[Scorpio-ZA]

Good Afternoon. 

My heading is a little short as I have a much longer question:

I am wanting to calculate the weighted average cost of an item being sold and then factor that result in subsequent calculations of the same item being sold again down the road. I will need to then build that measure into a running balance, but 1 problem at a time.

What I am looking for is to calculate the weighted average cost of a specific item based on all the transactions that preceded it. I am trying to figure it out, but I always keep bumping into a circular calculation and I can't work my way out of it.

I have attached an excel file which is basically what I am trying to recreate in PowerBI. In excel it is much easier as each cell is its own formula and all I do is point to previous lines, but in BI I need to program this into 1 DAX measure.

I have attached a Dropbox link to an excel file which is what I am wanting to recreate in PBI. The highlighted Cells are calculation cells and the calculation I am specifically trying to recreate in PBI is Column "I". You can run on the assumption that there are no other tables required.

https://www.dropbox.com/s/pt2g6jcpwlmhgu0/WA%20Cost.xlsx?dl=0

Thanking you in advance.

[lbendlin]

Your Excel table header area is, uhm, interesting.  You may want to clean that up.

Question on terminology: "cost of item being sold" - does that mean all P transactions in column F can be ignored?

[Scorpio-ZA]

Apologies for the headers, I designed this for me use only. I didn't think about how other people may read it. I have updated the headers and added a few comments to explain a few of my thoughts.

The P means purchase and S means Sale. I can't always rely on a negative number to mean sale as transfers out can take place which is not a sale/disposal and as such, I don't want it to confuse other reports I use for tracking sales.

[lbendlin]

Here are the results for products 4 and 5 over time.  The weighted average obviously breaks down when all quantity is sold. What should happen in such a scenario?

[Scorpio-ZA]

When all of the quantity is sold, the weighted average would need to become zero and be reset so that when the new tranch of the stock is purchased, it would then be a fresh WA.

Ultimately what I am trying to calculate and show on a table (which will be then used in other analytics), which is why I am starting with a WA calculation (using your screenshot below) for PROD 4. When the 822 is sold, the amount showing under the cost column is $43602.71 (Being the cost price of items sold) and not $43950.90 (Sale Price of items sold) showing a net cost of Zero, Net quantity of Zero and the WA will reset to Zero.

I am having issues with the DAX for some reason, I am getting invalid syntax when copying your code. Can you please link your file so I can have a look at it and figure out what I am doing wrong.

[lbendlin]

Here is the code.

avg c := 
var d=SELECTEDVALUE(WA[DATE])
var p=SELECTEDVALUE(WA[ SHARE ])
var s= sumx(filter(ALLSELECTED(wa),WA[DATE]<=d && WA[ SHARE ]=p),WA[ COST ])
var q= sumx(filter(ALLSELECTED(wa),WA[DATE]<=d && WA[ SHARE ]=p),WA[ QTY ])
return divide(s,q,0)

"When all of the quantity is sold, the weighted average would need to become zero and be reset so that when the new tranch of the stock is purchased, it would then be a fresh WA."

I tried for a couple of hours but I am not proficient enough in DAX to find a solution.  All I could find was a manual workaround to set the date filter accordingly (for example exclude all days until 2018-10-23) . Maybe some of the experts has a idea.

[lbendlin]

Thinking about it some more - If you can drop the requirement for a measure and instead an accept calculated columns for the cumulative quantity and the average cost to date then it will be possible to restart the average calculation after stock depletion. 

Would that be acceptable?

Note: I also noticed that you have individual days with zero sum (like 9/1/2017 for PROD 5) - this is messing up the computations royally.  Is it sufficient to have day level granularity, or do you need to do this by ID ?

[Scorpio-ZA]

A calculated column could work, but I have not really played with those too much, I've been delving into DAX more. I would not mind a link to the file so I can see how it has been laid out if it works out.

If I am understanding correctly. your query on the ID, I assume you are referring to column A in the excel. This can be ignored completely. It is for excel use only, it is for sorting other columns and then using it to restore the list to its original ordering.

I can live without the intra-day level granularity, it is something I am very used to in excel and does help when there is a purchase and sale on the same day. But I also assumed and accept that there are something things I will have to lose out on due to limitations - it was something I could not work out when I was starting the calculations when I was.