cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
bjsrm8 Frequent Visitor
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(
SUM('Monthly Ariba Dataset'[Adj QTY Received]),
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(
SUM('Monthly Ariba Dataset'[Adj QTY Received]),
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
Community Support Team

Re: Running Receipt Total Using String

Hi @bjsrm8,

Based on my test, you could refer to below steps:

1.Add index for your data in query editor:

1.PNG

2.Apply it and create below calculated column:

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

Result:

1.PNG

You could also download the pbix file to have a view.

 

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

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
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...


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
Community Support Team

Re: Running Receipt Total Using String

Hi @bjsrm8,

Based on my test, you could refer to below steps:

1.Add index for your data in query editor:

1.PNG

2.Apply it and create below calculated column:

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

Result:

1.PNG

You could also download the pbix file to have a view.

 

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.