Freight Cost per Unit - Invoice Cost Allocation

[RAQuest]

I am struggling with Invoice Freight Cost allocation per Unit.

My data consists of InvoiceNo, FreightAmt, ItemCode and Quantity Shipped (see table below). For example: Invoice 91 cost $23.18 to ship a total of 12 units or $23.18 / 12 = $1.93. The problem is the Freight Cost is allocated on the Inovice Level and I cannot sort out how to sum the qty shipped then divide by the Invoice Freight Amt. Any suggestions?

InvoiceNo	FreightAmt	ItemCode	QuantityShipped
91	23.18	Product 1	6
91	23.18	Product 2	6
92	330.36	Product 3	36
92	330.36	Product 2	36
92	330.36	Product 4	24
92	330.36	Product 5	24
92	330.36	Product 6	12
386	877.09	Product 4	100
386	877.09	Product 3	120
386	877.09	Product 5	100
386	877.09	Product 2	100
828	800.24	Product 4	120
828	800.24	Product 3	120
828	800.24	Product 5	120
828	800.24	Product 6	0
828	800.24	Product 2	120
902	428.16	Product 1	60
902	428.16	Product 7	60
902	428.16	Product 8	60
1054	116.79	Product 6	60
9	250	Product 9	1000

[dufoq3]

Add these steps below your last step:

    ChangedTypeLocale = Table.TransformColumnTypes(Source,{{"InvoiceNo", Int64.Type}, {"FreightAmt", Currency.Type}, {"ItemCode", type text}, {"QuantityShipped", Int64.Type}}, "en-US"),
    #"Grouped Rows" = Table.Group(ChangedTypeLocale, {"InvoiceNo"}, {{"FreightAmt", each List.Average([FreightAmt]), Currency.Type}, {"QuantityShippedTotal", each List.Sum([QuantityShipped]), Int64.Type}, {"All", each _, type table [InvoiceNo=nullable number, FreightAmt=nullable number, ItemCode=nullable text, QuantityShipped=nullable number]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"ItemCode", "QuantityShipped"}, {"ItemCode", "QuantityShipped"}),
    #"Inserted Division" = Table.AddColumn(#"Expanded All", "Freight Cost per Unit", each [FreightAmt] / [QuantityShippedTotal], Currency.Type)
in
    #"Inserted Division"

[v-jingzhan-msft]

Hi @RAQuest 

If you want to calculate freight cost per unit with Power Query, you can use Group by feature. You need to group your data by `InvoiceNo` to sum the `QuantityShipped` for each invoice and get the maximum `FreightAmt` for each invoice. 

Then add a custom column to divide the `FreightAmt` by `TotalQuantityShipped` as below. 

Number.Round([FreightAmt] / [TotalQuantityShipped],2)

Hope this helps. 

Best regards,

Jing

[v-jingzhan-msft · Accepted solution]

Hi @RAQuest 

You can create a new calculated column in your data table to calculate the freight cost per unit for each row. You can use the following DAX formula: 

DAX Freight Cost per Unit = [FreightAmt] / SUMX(FILTER('YourTableName', 'YourTableName'[InvoiceNo] = EARLIER('YourTableName'[InvoiceNo])), 'YourTableName'[QuantityShipped])

Replace 'YourTableName' with the actual name of your table.

Here's what this formula does:

- `FILTER` function is used to create a table that contains only the rows with the same `InvoiceNo`.

- `SUMX` function iterates over this filtered table and sums up the `QuantityShipped` for the invoice.

- The total freight amount (`FreightAmt`) for the invoice is then divided by this sum to get the freight cost per unit.

Once you have created the calculated column, you can use it in your reports to analyze the data. You can format it in Column tools ribbon. 

Best regards,

Jing

[RAQuest]

Jing - thank you so much for sharing your wisdom. More so, your explain of how and why your measure worked!