Weighted Average Cost in POWER QUERY

Question

Im new to power query and Im trying to calqulate Weighted Average Cost of materials.

When I receive a new stock, avarage price shoul be (Cost of Available Stock + Cost of New Stock) / Total Stock (Qty)

When we issue stock, the average price should be Cost of Available Stock / Available Stock (Qty)

I have following example table.

Is there any idea how to calculate the weighted average cost in this situation?

Thank you.

Answer 1

You can use the List.Generate function to generate both the Running Total Column as well as the Weighted Average Cost column.

Starting with:

M Code

let

//Change next line to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table11"]}[Content],
    
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Type", type text}, 
        {"In Price", Currency.Type}, {"Qty", Int64.Type}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type",{{"Type", Text.Proper, type text}}),
    #"Add Running Total Column" = 
        Table.FromColumns(
            Table.ToColumns(#"Capitalized Each Word") &
            {List.Generate(
                ()=>[rt=#"Capitalized Each Word"[Qty]{0}, idx=0],
                each [idx] < Table.RowCount(#"Capitalized Each Word"),
                each [rt = [rt] + #"Capitalized Each Word"[Qty]{[idx]+1}, idx=[idx]+1],
                each [rt])},
            type table[Date=date, Type=text, In Price=Currency.Type, Qty=Int64.Type, Running Total=Int64.Type]),

    #"Add Avg Cost Column" = 
        Table.FromColumns(
            Table.ToColumns(#"Add Running Total Column") & 
            {List.Generate(
                ()=>[cst=if #"Add Running Total Column"[Type]{0}="In" then #"Add Running Total Column"[In Price]{0} else null, idx=0],
                each [idx] < Table.RowCount(#"Add Running Total Column"),
                each [cst=if #"Add Running Total Column"[Type]{[idx]+1}="Out" then [cst]
                    else ((if [cst]=null then 0 else [cst]) * #"Add Running Total Column"[Running Total]{[idx]} + 
                        #"Add Running Total Column"[In Price]{[idx]+1} * #"Add Running Total Column"[Qty]{[idx]+1})
                        / #"Add Running Total Column"[Running Total]{[idx]+1} , idx=[idx]+1],
                each [cst])},
        type table[Date=date, Type=text, In Price=Currency.Type, Qty=Int64.Type, Running Total=Int64.Type, Avg Cost=Currency.Type])        
in
    #"Add Avg Cost Column"

results