Forum Discussion

Craterdee's avatar
Craterdee
Regular Visitor
1 year ago
Solved

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 i...
Need Help
  • Craterdee's avatar
    Craterdee
    1 year ago

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

     

    Now, I will actually go study the formulas 🙂

     

    Very appreciated. 

     

    Chrsitine

MFelix's avatar
MFelix
Super User
1 year ago

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's avatar
Craterdee
Regular Visitor
1 year ago

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

 

049238CAD2024-08-283050MEI 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

 

 

049031CAD2024-08-071650MEI 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's avatar
    MFelix
    Super User
    1 year ago

    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's avatar
      Craterdee
      Regular Visitor
      1 year ago

      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's avatar
        Craterdee
        Regular Visitor
        1 year ago

        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