cancel
Showing results for
Did you mean:
bjsrm8 Frequent Visitor

## Running Receipt Total Using String

Hello,

I'm a new DAX user and am having trouble getting a formula to transfer from Excel to PBI. I have tried two different methods based on responses from other forum posts. See bottom for what I'm envising as a final result.

My goal: create a running total column based on a Qty Received.

The excel formula I'm trying to recreate: =SUMIFS(\$C\$2:C3,\$A\$2:A3,A3,\$B\$2:B3,B3) where Column A = Order ID, Column B = PO Line Number, Column C = Qty Received

I think my issue is that in excel it is performing a SUM operation up until the row, where PBI is performing the SUM on the entire column?

Below are the two formulas that I have tried:

1. Running Receipt Total =
CALCULATE(
FILTER(
ALLSELECTED('Monthly Ariba Dataset'),
'Monthly Ariba Dataset'[[RCT]] Order Id]<=MAX('Monthly Ariba Dataset'[[RCT]] Order Id]) &&
'Monthly Ariba Dataset'[[PO]] PO Line Number] <= MAX('Monthly Ariba Dataset'[[PO]] PO Line Number])))

2. Running Receipt Total = CALCULATE(
ALL('Monthly Ariba Dataset'[[RCT]] Order Id]),
ALL('Monthly Ariba Dataset'[[PO]] PO Line Number]))

Below is an example of how I need to end result to be formatted.

Order ID | PO Line Number | Qty Received | Running Total

PO123    |   1                        |  -1                 | -1

PO123    |   1                        |  -1                 | -2

PO123    |   1                        |  -1                 | -3

PO123    |  1                         |   1                 | -2

PO123    |  1                         |   1                 | -1

1 ACCEPTED SOLUTION

Accepted Solutions Community Support Team

## Re: Running Receipt Total Using String

Hi @bjsrm8,

Based on my test, you could refer to below steps: 2.Apply it and create below calculated column:

`Running total = CALCULATE(SUM(Table1[Qty Received]),FILTER('Table1','Table1'[Index]<=EARLIER(Table1[Index])))`

Result: Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
jdbuchanan71 New Contributor

## Re: Running Receipt Total Using String

Is there something in the data set that would let you identify in what order the lines should be added into the running total.  Something like processed date/time or po line occurrence? Super User

## Re: Running Receipt Total Using String

Yeah, you may want to add an Index column in your Power Query and that would allow you to use EARLIER to filter your table to just rows up until the current row. See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

Proud to be a Datanaut! Community Support Team

## Re: Running Receipt Total Using String

Hi @bjsrm8,

Based on my test, you could refer to below steps: 2.Apply it and create below calculated column:

`Running total = CALCULATE(SUM(Table1[Qty Received]),FILTER('Table1','Table1'[Index]<=EARLIER(Table1[Index])))`

Result: 