Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
lexydraves
New Member

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 OrderSaler Order ItemMaterialOpen QtyTotal Stock
60218610640-41262-04550172
60382650640-41262-04550172
60492330640-41262-04572172

 

Trying to calculate the running stock total column:

Sales OrderSaler Order ItemMaterialOpen QtyTotal StockRunning Stock Total
60218610640-41262-04550172172
60382650640-41262-04550172122
60492330640-41262-0457217250

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?

1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

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 

vxinruzhumsft_0-1713323850574.png

 

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

vxinruzhumsft_1-1713323946700.png

 

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.

 

View solution in original post

2 REPLIES 2
v-xinruzhu-msft
Community Support
Community Support

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 

vxinruzhumsft_0-1713323850574.png

 

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

vxinruzhumsft_1-1713323946700.png

 

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
Frequent Visitor

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

 

Trevor_G_0-1712698392779.png

(_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): 

Trevor_G_4-1712698748546.png

 

 

Row Rank Calculated Column: 

Trevor_G_2-1712698484844.png

 

Final Table Data Listing:

Trevor_G_3-1712698552442.png

 

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. 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.