Forum Discussion
Help with data prep
You are indeed a super user!!! Thank you very much.
Now, I will actually go study the formulas 🙂
Very appreciated.
Chrsitine
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
Super UserTry 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%
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
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.