Column to calculate cost of unit

[aggie81]

I want to calculate the cost of a plant based on what the total costs to buy that plant from a supplier.
I have attached a workbook with what I am working that a supplier sent.
It is straight forward with most of the items with the exception when BOXING or COLD HOT fee is added in the column "Product".
I need to add the items that have the same ORDER NUMBER plus the BOXING or COLD HOT fee then divide by the number of items ordered to get a true cost per item.
The column labeled Costs is my attempt to do but it fails to get the correct answer.

Thanks,
Lee

 

[aggie81]

I figured it out by using a helper table by power query that contained only those invoice numbers with BOXING or COLD HOT. Then using IF and SUMIFS to get the correct price per unit.
Thank you for looking.

 

[Michael Gramm]

Try using this formula in the cost column. Does not account for 2 different items shipped on same order number.
=IFERROR(SUMPRODUCT(([Order '#]=[@[Order '#]])*[Total])/SUMPRODUCT(([Order '#]=[@[Order '#]])*[QTY Shipped]*([QTY Shipped]>1)),"Not Shipped")

 

[GraH - Guido]

You can do it all through PQ.
1. Load the data
2. Reference this query and count the items of each order + count if there is a "plus" item.
3. Reference the first query and combine with the second.
4. Expand the data and perform the final calculation.

// data
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order #", type text}, {"Purchase Order #", type text}, {"Status", type text}, {"Order Date", type datetime}, {"Ship Date", type datetime}, {"Week", Int64.Type}, {"Ship Via", type text}, {"Ship-to", type text}, {"Product #", type text}, {"JJL Item", type text}, {"Description", type text}, {"Supplier", type text}, {"Lot No.", type text}, {"QTY Ordered", Int64.Type}, {"QTY Shipped", Int64.Type}, {"QTY Outstanding", Int64.Type}, {"Total", type number}})
in
    #"Changed Type"

// Items_per_order
let
    Source = data,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"JJL Item", "Description", "Order #","Total"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Total] <> 0)),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",each [JJL Item],each if [JJL Item] = null then 0 else 1,Replacer.ReplaceValue,{"JJL Item"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each[Total], each if [JJL Item] = 0 then [Total] else 0 ,Replacer.ReplaceValue,{"Total"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value1", {"Order #"}, {{"Lines", each Table.RowCount(_), Int64.Type}, {"Items", each List.Sum([JJL Item]), type number}, {"Plus Cost", each List.Sum([Total]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Plus Cost per Item", each [Plus Cost]/[Items], type number)
in
    #"Added Custom"

// Result
let
    Source = data,
    #"Merged Queries" = Table.NestedJoin(Source, {"Order #"}, Items_per_order, {"Order #"}, "Items_per_order", JoinKind.LeftOuter),
    #"Expanded Items_per_order" = Table.ExpandTableColumn(#"Merged Queries", "Items_per_order", {"Plus Cost per Item"}, {"Plus Cost per Item"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Items_per_order", "Average Item Cost", each if [QTY Ordered] = 0 then null else ([Total]+[Plus Cost per Item])/[QTY Ordered])
in
    #"Added Custom"