Forum Discussion

RAQuest's avatar
RAQuest
New Member
2 years ago
Solved

Freight Cost per Unit - Invoice Cost Allocation

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 s...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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