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 (Qty Needed For Order Line - Shipped Qty) and I've added a "Desired Output for Remaining Qty" as what I am in need of for a calculated column. 


 

Need Help

4 Replies

  • 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?

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Tripb44 ,

     

    Check the column.

    Column = 
var min_index = CALCULATE(MIN('Table'[index]),FILTER('Table','Table'[ordernum]=EARLIER('Table'[ordernum])&&'Table'[orderline]=EARLIER('Table'[orderline])))
var order_qty = CALCULATE(SUM('Table'[sellingorderqty]),FILTER('Table','Table'[index]=min_index))
var ship_qty = CALCULATE(SUM('Table'[sellingshipqty]),FILTER('Table','Table'[ordernum]=EARLIER('Table'[ordernum])&&'Table'[orderline]=EARLIER('Table'[orderline])&&'Table'[index]<=EARLIER('Table'[index])))
return
order_qty-ship_qty

     

    Best Regards,

    Jay