Forum Discussion
Help with data prep
Hi gang,
I am attempting to "clean" my data so that I can get stats by truck, by proportionate share.
1. How can I get a list of orders by truck no. (so if an order has 3 trucks assigned to it, I would lie 3 lines instead of 1) If I split data, then I end up with as many columns as I have trucks for any given order
2. How can I assign a % of revenue to each order per truck (see below - CITY alone = 100% / HWY alone = 100% / 1 HWY = 70% with 1 CITY = 30% // So if two HWY trucks + 1 City truck, % should be 35-35-30)
I am stuck.
Thank you all for your help 🙂 Chrisitne
Here is my data table:
| WO # | Currency | Delivery date | Amount | Truck |
| 048976 | CAD | 2024-08-05 | 675 | MEI Truck 102; MEI Truck 22 |
| 049001 | CAD | 2024-08-02 | 400 | MEI Truck 103 |
| 049099 | CAD | 2024-08-15 | 1800 | MEI Truck 103 |
| 049123 | CAD | 2024-08-16 | 250 | MEI Truck 103 |
| 049031 | CAD | 2024-08-07 | 1650 | MEI Truck 103; MEI Truck 22 |
| 049089 | CAD | 2024-08-19 | 600 | MEI Truck 103; MEI Truck 23 |
| 049103 | CAD | 2024-08-19 | 1300 | MEI Truck 103; MEI Truck 23 |
| 049115 | CAD | 2024-08-19 | 1850 | MEI Truck 103; MEI Truck 23 |
| 049144 | CAD | 2024-08-19 | 440 | MEI Truck 103; MEI Truck 23 |
| 049164 | CAD | 2024-08-22 | 900 | MEI Truck 103; MEI Truck 23 |
| 049196 | CAD | 2024-08-23 | 500 | MEI Truck 103; MEI Truck 23 |
| 049203 | CAD | 2024-08-26 | 1350 | MEI Truck 103; MEI Truck 23 |
| 049265 | CAD | 2024-08-30 | 2000 | MEI Truck 103; MEI Truck 23 |
| 049238 | CAD | 2024-08-28 | 3050 | MEI Truck 103; MEI Truck 23; MEI Truck 38 |
Master data:
| ID | Type |
| 22 | HWY |
| 23 | HWY |
| 25 | HWY |
| 30 | HWY |
| 34 | HWY |
| 35 | HWY |
| 37 | HWY |
| 38 | HWY |
| 39 | HWY |
| 40 | HWY |
| 41 | HWY |
| 42 | HWY |
| 43 | HWY |
| 104 | City |
| 105 | City |
| Combo | 1 | 2 | 3 | 4 | 5 | |
| City | 100% | 100% | ||||
| City - City | 50% | 50% | 100% | |||
| City - City - HWY | 15% | 15% | 70% | 100% | ||
| City - City - HWY - Sub | 15% | 15% | 35% | 35% | 100% | |
| City - City - HWY - HWY | 15% | 15% | 35% | 35% | 100% | |
| City - City - HWY - HWY - HWY | 15% | 15% | 23% | 23% | 23% | 100% |
| City - HWY | 30% | 70% | 100% | |||
| City - HWY - HWY | 30% | 35% | 35% | 100% | ||
| City - HWY - HWY - HWY | 30% | 23% | 23% | 23% | 100% | |
| City - HWY - HWY - HWY - HWY | 30% | 18% | 18% | 18% | 18% | 100% |
| City - HWY - HWY - Sub | 30% | 23% | 23% | 23% | 100% | |
| HWY | 100% | 100% | ||||
| HWY - HWY | 50% | 50% | 100% | |||
| HWY - HWY - HWY | 33% | 33% | 33% | 100% | ||
| Agent | 100% | 100% | ||||
| City - Sub | 30% | 70% | 100% | |||
| HWY - Sub | 50% | 50% | 100% | |||
| Sub | 100% | 100% | ||||
| HWY - HWY - Sub | 33% | 33% | 33% | 100% | ||
| City - HWY - Sub | 30% | 35% | 35% | 100% | ||
| City - City - Sub | 15% | 15% | 70% | 100% |
You are indeed a super user!!! Thank you very much.
Now, I will actually go study the formulas 🙂
Very appreciated.
Chrsitine
7 Replies
Hi Craterdee ,
I don't understand the question about the hierarchy but concerning the number of rows you should do the following in Power Query:
- Select the Truck colum
- Go to transform
- Select Split columns
- Split by delimiter ", "
- Select advance and split to rows
This will give you the following result:
Now you can have the number of rows by WO.
Can you share some more information about the hierarchy please.
Thank you for that information!
Next, I need to determine the % of sales for each truck in any given order.
In this case, Order # 49238 has 3 trucks assigned to it - 103 is CITY / 23 is HWY / 38 is HWY therefore I need 30% of the sale to be assigned to truck 103, 35% (1/2 of 70%) to be assigned to 23 and 35% to be assigned to 38
049238 CAD 2024-08-28 3050 MEI Truck 103; MEI Truck 23; MEI Truck 38 In this case, Order # 49031 has 2 trucks assigned to it - 103 is CITY / 22 is HWY therefore I need 30% of the sale to be assigned to 103, and 70% to be assigned to 22
049031 CAD 2024-08-07 1650 MEI Truck 103; MEI Truck 22 If I have 2 city trucks and 3 HWY trucks assigned to an order, I would need calculate 15% of sales for each city truck and (70%/3) of sales for each HWY truck
City trucks must total 30% of the order's sale amount / HWY trucks 70%
Does this better describe my issue?
Thanks, Christine
Try the following two measures:
Calculation = var _CITY = 0.30 var _HWY = 0.70 var _TruckCount = CALCULATE(COUNTROWS(WO), WO[WO #] = SELECTEDVALUE(WO[WO #]), REMOVEFILTERS(MasterData[ID])) var _Result = IF(SELECTEDVALUE(MasterData[Type]) = "City", DIVIDE(_CITY, _TruckCount), DIVIDE(_HWY, _TruckCount) ) RETURN _Result Final Value = SUMX(WO, [Calculation])I have one question that is about when there is only one type of truck how do you split is it based on the 100%
