Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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?
Solved! Go to Solution.
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 @lexydraves , 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.
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 @lexydraves , 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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
109 | |
104 | |
103 | |
87 | |
61 |
User | Count |
---|---|
162 | |
132 | |
131 | |
95 | |
86 |