Hello @Thorfirrr,
Can you please try:
1. Create a DAX measure that calculates the recommended stock quantity per Client_ID, Product_ID, and Size, as you already mentioned you have.
2. Load the box data into Power BI as a table ("BoxData")
3. Create a new table ("Assignments,") that will store the box combinations for each recommendation, with columns: Client_ID, Product_ID, Size, BoxCombination, and Efficiency.
4. Write a Power Query (M) function that will generate all possible combinations of boxes from the "BoxData" table for a given Client_ID, Product_ID, and Size.
let
GetCombinations = (boxList as list, targetQuantity as number, currentCombination as list, combinations as table) as table =>
let
currentItem = List.First(boxList),
remainingItems = List.RemoveFirstN(boxList, 1),
currentItemQuantity = currentItem[SIZE QUANTITY],
newItem = [BOX_ID = currentItem[BOX_ID], SIZE = currentItem[SIZE], SIZE QUANTITY = 1],
updatedCombination = if currentCombination = null then {newItem} else currentCombination & {newItem},
updatedQuantity = List.Sum(Table.Column(updatedCombination, "SIZE QUANTITY")),
newCombinations = if updatedQuantity = targetQuantity then Table.InsertRows(combinations, 0, {updatedCombination})
else if updatedQuantity < targetQuantity then GetCombinations(remainingItems, targetQuantity, updatedCombination, combinations)
else combinations,
newCombinations = GetCombinations(remainingItems, targetQuantity, currentCombination, combinations)
in
newCombinations,
AssignedBoxes = Table.FromRecords(GetCombinations(BoxData, TargetQuantity, null, {}))
in
AssignedBoxes
5. Use DAX to call the Power Query function for each recommendation and calculate the efficiency of each box combination.
Assignments =
VAR RecommendedStock = [YourRecommendedStockMeasure]
VAR TargetSize = [Size]
VAR TargetProduct = [Product_ID]
VAR TargetClient = [Client_ID]
VAR Combinations = GetCombinationsByTarget(TargetClient, TargetProduct, TargetSize, RecommendedStock)
VAR MaxEfficiency = MAXX(Combinations, [SIZE QUANTITY] / RecommendedStock)
VAR MaxEfficiencyCombination = FILTER(Combinations, [SIZE QUANTITY] / RecommendedStock = MaxEfficiency)
RETURN
CONCATENATEX(
MaxEfficiencyCombination,
[BOX_ID],
", "
)
Should you require further assistance please do not hesitate to reach out to me.
