How to allocate shipping cost per order down to each order row

[ErikOmni]

Hi!

So I've been struggling with an issue now for quite a while. 

What I have: I have shipping cost per order (unique orderID for each order) in one table and orderdata with each row representing an ordered item in a different table. I want to allocate the cost per order down to each order row.

I've done a couple of different attempts at this (calculated columns, measures to calculate total order rows) some of them kind of do the trick, but I keep running up on different issues when I slice my analytical work into different dimension. Basically, the numbers doesn't fully add up.

So I have come to the conclusion that I need to solve it by creating custom columns in the table with the Orderdata.

What I want to do: I have created a custom column called "Shipping cost adjusted", which is the the total Shipping cost for the orderID for all order row types that contain "Product" (some contain "Freight"). Next step should be to create a custom column for the total number of order rows for the unique OrderID. Thereafter I can create a new custom column dividing Shipping cost adjusted / total number of order rows for the unique ID. That should make it 100% waterproof that cost is allocated properly on SKU level. See below for example. 

OrderID 40086 contains three order rows. So the custom column should return a 3 on each order row for 40086. That gives me a cost of 15 per SKU, and once I sum it up I get the total cost of 45.

Any thoughts on how to do this?

[davehus]

Hi @ErikOmni, Can you send me a sanitised version of the data in a table. So I have the full structure and I'll see what I can do. 

[ErikOmni]

@davehus 

Would this work:

https://www.dropbox.com/s/4szxu3611qkq23k/Data_Example_OrderRows.csv?dl=0

[davehus]

Hi @ErikOmni ,

Create a custom column in Power Query like below.

List.Count(Table.SelectRows(

                        #"Changed Type", //Comment change to previous step in your power query

                        each [OrderID]=[OrderID]

                    )[OrderID])

Hope this helps

Did I help you today? Please accept my solution and hit the Kudos button.

[ErikOmni]

@davehus Hi Dave! Thanks for giving it a shot. However, this didn't work (or I executed it incorrectly). It returns all order rows for the entire order data. See below:

Any thoughts on that?

[davehus · Accepted solution]

Hi @ErikOmni ,

Had to rethink this one as I'd missed step in the previous code and when I added it the performance wasn't the best. Please see code below in the excel you sent me. You can go to advanced editor and copy it into your model.

Did I help you today? Please accept my solution and hit the Kudos button.

[ErikOmni]

Thank you! @davehus