Calculate running sum of measure and use it to determine how many rows are shown

[DanielB_NL]

Hi all,

The purpose of my visual is to create a Reminder overview for our supplier so he can see which of our purchase orders per item no. are overdue. I have a matrix in which, among others, I show per Item no. the Total amount needed ASAP (don't mind the mispelled amounD... :-)..) and after that I show the related Purchase order no., pos. no. (line no. within the purchase order), and the Amount open on order. The rows with purchase order info are sorted on the Delivery date confirmed (not included in the matrix, see image 2).

Now, I only want to show the related purchase orders as long as the running sum of Amount open on order is less or equal to the Total amount needed ASAP.  So in the first screenshot below I want to filter out the lines that are marked red. 

Question: can anyone help me out with this particular case? I've tried several tutorials about creating a running sum, but can't make it work... Thanks in advance!

[v-chenwuz-msft]

Hi @DanielB_NL ,

Sorry don't really understand how you are calculating cumulatively.
Can you explain why the amount open on order of 109980 is greater than the asap. But it is marked as red?

Best Regards

Community Support Team _ chenwu zhu

[DanielB_NL]

Hi @v-chenwuz-msft , 
The idea is that I remind my supplier which planned deliveries I need as soon as possible: 

• For my production I need (as soon as possible) the amount of items in 'Total amount needed a.s.a.p.', example: of item 6701-0019 we need 4 pieces.
• The purchase department has ordered the item with our supplier multiple times. The rows with purchase order information are rows that are already later than promised by my supplier, so they are too late.
  Example: for item 6701-0019 there are three purchase orders which are still open and too late
• Then, I need to check which purchase orders are needed to cover the amount that I need as soon as possible
  Example: for item 6701-0019 I need 4 pieces, and to cover this amount I only need the first purchase order (purchase order no. 109452). And I can neglect the last two rows (that's why I made them red)
  However, for item 6701-0033 I need 89 pieces ASAP, and to cover this amount I need three rows: 
  Purchase order no's 108465, 109082 and 109195, because the open amount on these rows is 20+40+60=120 and this is enough to cover the needed 89. So again I can neglect the other rows.
• So, I need to compare the running total of 'Amount open on order' with the column 'Total amount needed a.s.a.p'.
• And I need to show rows with Purchase Orders until the Running total is equal to or bigger than the 'total amount needed a.s.a.p.'

I hope this will clarify the situation and that you can help me out!