Forum Discussion
Resolver IIIWeighted Average Cost in POWER QUERY
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.
8 Replies
ronrsnfldSuper User
AUDISUThank you. 😊
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?
- AlB
Community Champion
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
