Weighted Average Cost in POWER QUERY

[AUDISU]

Hi All,

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 calqulate the weighted average cost in this situation?

Thank you.

[ronrsnfld · Accepted solution]

See Weighted Average Cost in Power Query 

[Youcef_Data]

You can try this script in power query

"
let
//Change next line to reflect your actual data source
Source = Excel.CurrentWorkbook(){[Name="PMP_5"]}[Content],

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date},
{"In Price", Currency.Type}, {"Qty", Int64.Type}, {"ItemID", type text}}),

#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}, {"Qty", Order.Descending}}),

#"Grouped Rows" = Table.Group(#"Sorted Rows", {"ItemID"}, {
{"All", each _, type table [Date=date, Type=text, In Price=Currency.Type, Qty=Int64.Type, ItemID=text]}
}),

#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let
currentTable = [All],
#"Add Running Total Column" =
Table.FromColumns(
Table.ToColumns(currentTable) &
{List.Generate(
()=>[rt=currentTable[Qty]{0}, idx=0],
each [idx] < Table.RowCount(currentTable),
each [rt = [rt] + currentTable[Qty]{[idx]+1}, idx=[idx]+1],
each [rt])},
type table[Date=date, In Price=Currency.Type, Qty=Int64.Type, ItemID=text, Running Total=Int64.Type]),

#"Add Avg Cost Column" =
Table.FromColumns(
Table.ToColumns(#"Add Running Total Column") &
{List.Generate(
()=>[cst=if currentTable[Qty]{0}>0 then #"Add Running Total Column"[In Price]{0} else null, idx=0],
each [idx] < Table.RowCount(#"Add Running Total Column"),
each [cst=if currentTable[Qty]{[idx]+1}<0 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, In Price=Currency.Type, Qty=Int64.Type, ItemID=text, Running Total=Int64.Type, Avg Cost=Currency.Type])
in
#"Add Avg Cost Column"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"All"}),

#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Date", "In Price", "Qty", "Running Total", "Avg Cost"}, {"Date", "In Price", "Qty", "Running Total", "Avg Cost"}),
#"Rounded Off" = Table.TransformColumns(#"Expanded Custom",{{"Avg Cost", each Number.Round(_, 2), type number}})
in
#"Rounded Off"
"

[ronrsnfld]

@Youcef_Data Could you please add an explanation of how your solution improves on the one I supplied a year or two ago?

[Youcef_Data]

In the given script, there is a grouping operation based on the "ItemID" (material) and then the calculations for running total and weighted average cost are performed within each group. This suggests that the calculations are done separately for each distinct "ItemID" (material) .🙏

[ronrsnfld]

Thank you for clarifying. I guess the OP was either happy with the grouping, or was able to adapt it to his actual data.

[AUDISU]

Thank you. 😊

[AlB]

Hi @AUDISU 

1. Are you sure you want to do this in Power Query? Why?

2. Please show the expected result based on the sample data provided

3. Please share the sample data in text-tabular format instead of a screencap so the contents can be copied

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AUDISU]

Hi,

Thank you for your reply.

Yes . I want to calculate the total cost of issued items base on wieghted average coast.

Date              TypIn Price  Qty Running TotalAvg Cost(Per Unit)Total Cost 

1/01/2022	In	2	1000	1000	2.00	-
2/01/2022	Out		-200	800	2.00	400.00
15/01/2022	Out		-300	500	2.00	600.00
3/02/2022	In	3	1000	1500	2.67	-
8/02/2022	Out		-500	1000	2.67	1,333.33
10/02/2022	Out		-100	900	2.67	266.67