I have the attached model in pbix where you will see a simplified model of bill of material orders, so basically ordering the most granular products of an order. I’m currently having with the cumulative total
of “ordered” quantities based on a ranking of orders.
Model:
Order -> Order Lines (qty) > Products -> Product BOM (qty) -> Bom Items.
Ordered quantities = basically the cumulative total of Orderlines Qty * Product BOM Qty, ranked by a custom ranking (based on the expected delivery date of the order).
My cumulative total is nicely adding up the totals using the ranking, but not properly by BOM item.
=> I seem to have difficulty to add another dimensions to the calculate filter.
Off topic:
Notice if you want to multiply fields across multiple dimensions, it only works if you do calculate(sum(value)),TheOtherEntity). I don’t get what “TheOtherEntity” does to the filter context of the current, but it only works properly if you do it this way.
Though you have tried to explain the scenario but somehow it is still i am not able to figure out as what exactly you are trying to achieve. Can you send a excel upload of the exact output you are looking for?? Try to use same name in excel too.
Thanks for your reply, the goal is to have:
a cumulative total by “Order Lines”[Orderline Qty] * “Product Bom”[Bom Qty] ranked by the “Order”[Expected Delivery" for each “Product Boms”[Bom Item]
Normally it would be something like:
Cumulative Total Order Quantity by Order Rank =
VAR __CurrentOrderRank = [Order Rank Expected Delivery]
VAR __CurrentItem = SELECTEDVALUE(‘Bom Items’[Bom Item])
RETURN
CALCULATE(
[Total Ordered Quantity];
FILTER(
ALL(Orders);
[Order Rank Expected Delivery] <= __CurrentOrderRank
[Bom Item] = __CurrentItem
=> of course that’s not possible as you’re filtering the “Orders”
)
So in the excel:
Blue = Rank to be used for the cumulative total (it’s the outcome of a measure)
Green = The Bom item which I’m trying to get for each the cumulative total (and per order)
Grey= multiplication of Orderline Qty & Bom Qty
Yellow (the corrected OUTCOME) = the cumulative total corrections. So you see that some are correct, some aren’t.
I believe you need to correct your Datamodel. As of now there is no way to connect your Product bom can get information from orders table. That you are trying to do is that you are taking orders from order tables,order line,orderline qty & product from orderline table and also the bom item from product bom table. You have defined your ranking based on order table.
Now try to do this and you will get a good picture. create a new table visual and bring few columns from orderline and any column from products bom table.
This scenario is still feasible if you can find out a way to get calculated column in product bom table that can tell which line ltem in product bom table belongs to which order.
Each orderline has a product with a quantity. That Product is associated with multiple items (bom) of which each with a quantity. So basically, those items can be across multiple products but in the end, the quantities coming from the order lines and which are multipled by the item’s quantities which make up the product. That can be done using the current hierarchy/model. Those calculations works 100%. The problem is the cumulative total itself of that multiplication. It either needs to be summarized first (or something), but the model itself is a simplified version of what you would find typically for orders, products & boms.
The real aim is to then compare the outcome of the cumulative total with the stock so you can see which orders have enough stock to fulfill each of those items.
Yes you are right the model as is can answer your calculation. In your excel screenshot you need a cumulative total that require to check the bom item across an order which is simply not possible with current data model.
Also, did you try to build the table visual as i suggested with the order line and product bom ? As soon as a bom item gets a reference to a order it will be easier to write a logic for the same. This is what i can think of at this moment after going through your model.
Hi @Wlknsn. As @Hemantsingh suggests, I also think the data model should be reviewed … any bi-directional relationships, let alone a number of them, can make DAX respond erratically and difficult to analyze and should be avoided if at all possible. Perhaps if you could prepare a mock-up in Excel of exactly what output you’re looking for with a small set of your data it would help clarify things and be a way forward. Greg
The excel is already in my post with the output, but in meantime I’ll take out the excess entities which will result in something like this:
Total ordered quantity = Order line qty * Bom Qty.
What is needed now is the cumulative total of “total ordered quantity”, by order, by Bom item, ranked descending by the measure: Order Rank (it’s in the file).
I think I’ve been able to simplify your data model a bit; the [Products] table isn’t needed for this calculation. As well, using the ordering measure create some difficulties, so I put the expected delivery date directly in the table and sorted by that instead.
With that, I was able to create a measure that calculates the cumulative total as per your sample:
Cumulative Total Ordered Quantity by BOM Item =
VAR _CurrentExpectedDeliveryDate = SELECTEDVALUE( Orders[Expected Delivery] )
VAR _CurrentOrderedQuantity = [Total Ordered Quantity]
VAR _PreviousOrderedQuantity =
CALCULATE(
[Total Ordered Quantity],
ALL( Orders ),
Orders[Expected Delivery] < _CurrentExpectedDeliveryDate
)
VAR _Result =
IF(
ISBLANK( [Total Ordered Quantity] ),
_CurrentOrderedQuantity,
_PreviousOrderedQuantity + _CurrentOrderedQuantity
)
RETURN
_Result
But you want the third row to be 36 + 42 + 48 = 126, right, and not the 90 shown in your sample, or are you only looking for the most recent and current quantity?
It should be for bom item R4:
The first ranked order qty = 36
The second ranked order =36 + 6 = 42
The third ranked order = 42 + 48 = 90
Now, I actually need that ranking measure also as in the original document, it’s actually based on quite a lot of criteria, but that part is working now very well.
The change in model, that’s fine but I’ve never worked with a direct many to many before (always using bridging tables in between which should be pretty common).
Anyway, the overall goal should be to have the per order, per bom item the ordered quantity and this cumulated as well. The cumulative factor enables one to see how many orders you could fulfill of each bom item.
The complexity here is that :
the total quantity is based on current row multiplied by a parent 2 levels up (in the original model)
the ranking used of the cumulated total is based on a calculation based on the cirteria of the highest parent.
OK … I’ll try again … so you’re not looking for a cumulative total (the current value plus all previous), rather a running total (only the current entry plus the most recent). As i said, I didn’t have success the first time with the ranking measure directly and substituted the expected delivery date, but, again, I’ll try again…
I’m afraid I don’t understand your final statement: “Anyway, the overall goal…” Is this reflected in your Excel sample? Does this change the “90” value from what’s shown in the Excel sample?
Edit: oops … please ignore the earlier comment on cumulative vs. running … the penny just dropped
My point is about the complexity that’s there. In fact, it shouldn’t be that hard, but I believe I’m missing something …
For each bom item, go and get the ordered quantity of the order line of current order. Multiply this with bom item qty. Finally, do cumulative total of that total at bom item level based on the ranking of the orders. Just tried that on the EDNA bot … no luck.
I unfortunately haven’t got it working yet, and need to return to that “pesky” day job, so I’m passing this issue back to the forum … hopefully someone else will be more helpful. I’ll keep this in mind, and post if I find anything else. Sorry, Greg
@Melissa could you please help? Based on what @Greg said, indeed, the model can be simplified by taking out a level. I’ve attached an very easy model but just can’t get it to work.
First as a reference I’ve created a summary Test table in Power Query.
You will find that the Qty doesn’t match the provided expected outcome. this is due to the fact that you are summing both Sales Qty and BOM Qty on an Sales Order level before multiplication.
Cum Total BOM Quantity by Order Rank =
VAR __CurrentOrderRank = [Order Rank Expected Delivery]
VAR __CurrentItem = SELECTEDVALUE('Bom Items'[Bom Item])
RETURN
CALCULATE(
[BOM Item Quantity by Order Rank],
FILTER(
ALL(Orders),
[Order Rank Expected Delivery] <= __CurrentOrderRank
)
)
