Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Running total row

I am stuck on how to get a running total based on the previously returned/calculated value. I am running into circular dependcies issues mostly. Sample data below: Sales Order Saler Order Item ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,

    Thanks for the solution Trevor_G  provided, but your solution have some mistakes that the user want to substract the total open qty, so i want to offer some more information.

    hello Anonymous , based on your description, you can refer to the folloing solution.

    Sample data 

     

    1.Create a rank measure

    Rank = RANKX(ALLSELECTED('Table'),CALCULATE(SUM('Table'[Sales Order])),,ASC)

    2.Create the running total measure

    Running Stock Total =
VAR _totalstock =
    SUM ( 'Table'[Total Stock] )
VAR _rank = [Rank]
VAR _totalopenqty =
    SUMX (
        FILTER ( ALLSELECTED ( 'Table' ), [Rank] <= _rank && [Rank] <> 1 ),
        [Open Qty]
    )
RETURN
    _totalstock - _totalopenqty

    Output

     

    Best Regards!

    Yolo Zhu

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

     

Trevor_G's avatar
Trevor_G
Frequent Visitor
2 years ago

If you have some way to dertermine how the rows should be ordered, you can try this measure: 

 

(_RowRank can be date, order number, or any other criteria that you want to sort by)

 

 

Results (based on ranking rows by Sales Order, found in _RowRank): 

 

 

Row Rank Calculated Column: 

 

Final Table Data Listing:

 

The _RunningTotal measure can provide any running totals. But will always need a way to determine x <= MAX(x). 

This is most often used with things like dates to get cumulative curve data over time, but it can work with anything similar.