Help with data prep

[Craterdee]

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%

[MFelix]

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.

[Craterdee]

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

[MFelix]

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%

[Craterdee]

Hi Miguel!

Thank you for the time you are taking to help me with this problem.

Order no. 49238 is not right, there are 2 HWY trucks therefore each should account for 35% of the sale (because total % alloted to HWY is 70%) - If my order had 3 HWY trucks, I would need 70% / 3 so 23.33%

Sometimes an order has 3 HWY and 2 CITY - then I would need (70%/3 and 30%/2)

There is no consistency in how many trucks , and which type, are assigned to each order

When there is only 1 truck, whether CITY only or HWY only, 100%

Thanks you 🙂

Christine

[Craterdee]

Hi again Miguel,

Additional details / clarifications: 

Once the proper % has been assigned in each order based on # of trucks and type of truck, the % sale amount / truck needs to be calculated.

Order 49238:

Sale = 3,050.00 Therefore

3050 x 0.35 = 1067.50

3050 x 0.35 = 1067.50

3050 x 0.30 = 915

Thanks!

Christine

[MFelix]

Hi @Craterdee ,

I have made some changes using some auxiliary calculations add the following measures:

% Calculation = 

 var _CITY = 0.3 
var _HWY = 0.7 
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


% Calculation 1 Type = 

 var _CITY = 1 
var _HWY = 1 
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

Type Count = 
var _TypeCount =  CALCULATE(  COUNTROWS(  SUMMARIZE(WO, WO[WO #], MasterData[Type])), ALLSELECTED(MasterData[ID]))
RETURN
_TypeCount

Final Value % = 
        IF(
			[Type Count] = 1 && [% Calculation] <> BLANK(),
			SUMX(
				SUMMARIZE(
					WO,
					WO[WO #],
					MasterData[Type],
					MasterData[ID]
				),
				[% Calculation 1 Type]
			),
			SUMX(
				SUMMARIZE(
					WO,
					WO[WO #],
					MasterData[Type],
					MasterData[ID]
				),
				[% Calculation]
			)
		)

In the mockup I have made some changes in order to have a single type with 2 or more trucks in this case 

add 3 rows 999999, 999998, 999997

Has you can see all the examples are there and believe they are properly calculated now just add the following measure for the total value:

Total Amount = SUM(WO[Amount])

Total Amount Value = IF(ISINSCOPE(MasterData[ID]), [Total Amount] * [Final Value %], DIVIDE([Total Amount], COUNTROWS(VALUES(WO[TruckID])
)))

See PBIX file attach.

[Craterdee · Accepted solution]

You are indeed a super user!!!  Thank you very much.

Now, I will actually go study the formulas 🙂

Very appreciated. 

Chrsitine