Forum Discussion

Tripb44's avatar
Tripb44
Helper II
4 years ago

Running Total for Calculated Column

I am trying to calculate the remaining qty needed to be shipped per order -> per line.  Below is an example of the date structure with "Current Output for Remaining Qty" as a column simply taking (Qt...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Tripb44 

 

You are looking at order number and order line, right? Can you add an index column in Power Query? one way...

Remaining Qty = 
VAR totalShippedQty=SUMX(FILTER('Table','Table'[OrderNum]=EARLIER('Table'[OrderNum])
    &&'Table'[OrderLine]=EARLIER('Table'[OrderLine])
    &&'Table'[Index]<=EARLIER('Table'[Index]))
    ,'Table'[Shipped Qty])
RETURN
'Table'[Qty Needed]-totalShippedQty

 

Tripb44's avatar
Tripb44
Helper II
4 years ago

Hi, thanks for your comment. I have pasted a snip of the output using live data.  Highlighted in red is my current output, highlighted in green is my desired output for the “Rem Qty” and “Rem Total” columns.  Highlighted in orange is the output of your recommended calculated column.  Above the excel output is the calculated column dax for your recommendation.  In answer to your question, yes, I want to group by order number and order line.  Each order line can have multiple order releases such as the example I am showing here.  Desired output and use for this will be to have a date interval selected and capturing the remaining qty left to be shipped and the associated dollar value.  Thank you for your time and help on this, it is greatly appreciated.




 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Tripb44 

     

    It's wierd...can't repreduce your result, added two rows for testing, or does column Rank matter?