cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
ErikOmni
Helper I
Helper I

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

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.

 

ErikOmni_0-1663790534817.png

 

Any thoughts on how to do this?

1 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.

View solution in original post

6 REPLIES 6
davehus
Memorable Member
Memorable Member

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. 

davehus
Memorable Member
Memorable Member

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.

@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:

 

ErikOmni_0-1663829716209.png

 

ErikOmni_1-1663829721998.png

 

Any thoughts on that?

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.

Thank you! @davehus 

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors