Cumulative Total with multiple levels and ranked by measure

[wlknsnBI]

I posted this earlier, but I reduced some complexity as the model wasn't ideal.

I have the following model:

I'm trying to get the cumulative total quantity by each order of each bom item ranked by measure order. The output should be something like:

The complexity comes from having multiple levels.

Hereby also the  pbix 

[v-lionel-msft · Accepted solution]

Hi @wlknsnBI ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wlknsnBI]

Hi,

the total quantities are now no longer correct but your measure is (I can fix those quantities myself). 

Nice solution by the way!

Big thanks.

[amitchandak]

@wlknsnBI , Please find the attached file after the signature. Check for measure

[wlknsnBI]

Hi there, this is promising however, the cumaltive total is now no longer ranked by a measure. The measure which should rank the cumulative total is  Order Rank Expected Delivery =

// Rank to calculate the Order prioritization for stock requirements.

VAR __CurrentSelection =

SELECTEDVALUE ( Orders[Expected Delivery] )

VAR __DatesGreaterThanCurrentSelection =

FILTER ( ALL (Orders[Expected Delivery] ), Orders[Expected Delivery] <= __CurrentSelection )

VAR Result =

COUNTROWS ( __DatesGreaterThanCurrentSelection )

RETURN

Result

When I replace what you did here (in red), the total nu longer calculates correctly:
(and I need to have this by measure because the real sample has a complex ranking based on multiple criteria, so it can't be based on existing columns)

Measure =

VAR _Rank = [Order Rank Expected Delivery]

RETURN

CALCULATE(

CALCULATE(

sumx(

Orders,

[Total Ordered Quantity]

),

VALUES('Product Boms'[Bom Item]),

VALUES(Orders[Order])),

FILTER(ALLSELECTED(Orders),

[Order Rank Expected Delivery] >= _Rank),

FILTER(ALLSELECTED('Product Boms'), 'Product Boms'[Bom Item] =max('Product Boms'[Bom Item])))

Any idea how to resolve this?