Forum Discussion
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 | Material | Open Qty | Total Stock |
| 602186 | 10 | 640-41262-045 | 50 | 172 |
| 603826 | 50 | 640-41262-045 | 50 | 172 |
| 604923 | 30 | 640-41262-045 | 72 | 172 |
Trying to calculate the running stock total column:
| Sales Order | Saler Order Item | Material | Open Qty | Total Stock | Running Stock Total |
| 602186 | 10 | 640-41262-045 | 50 | 172 | 172 |
| 603826 | 50 | 640-41262-045 | 50 | 172 | 122 |
| 604923 | 30 | 640-41262-045 | 72 | 172 | 50 |
I have sorted sales order and saler order item in ascending order already in m query. For each material, the first sales order/item should return the total stock value, so 172 in this case. The next row for the material should return the previously returned/calculated running stock total minus open qty, so 172-50=122. The next row would also be the previously returned/calculated running stock total minus open qty so 122-72=50 and so on. Is there any way to do this with DAX or m query?
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 - _totalopenqtyOutput
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.
2 Replies
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.
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 - _totalopenqtyOutput
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.
