Solved: Running Total - Page 2

Lucy64 · ‎07-18-2016

I am trying to calculate a running total for a production line based on the Machine name, there is no date value. I am using a Direct Query to SQL database. I want to add an exta column for running total of FNV, is this possible?

jahida · ‎07-19-2016

Eyy, I think I got it. Try this:

RunningTotal2 = MAXX(Table1, SUMX(
FILTER(
	SUMMARIZE(CALCULATETABLE(Table1, ALLEXCEPT(Table1, Table1[Building]), ALLSELECTED(Table1[Building])), Table1[MachineName], "NetVal", SUM(Table1[Fabrication Net Value])),
	[NetVal] >=
		SUMX(FILTER(CALCULATETABLE(Table1, ALLEXCEPT(Table1, Table1[Building]), ALLSELECTED(Table1[Building])), Table1[MachineName]= EARLIER(Table1[MachineName], 2)), Table1[Fabrication Net Value]))
, [NetVal]))

Hopefully you can see how that would scale out to other columns if you had multiple filters.

View solution in original post

joglidden · ‎07-18-2016

Where would the running total start, and what would be the next value added to the total?

Lucy64 · ‎07-18-2016

I would sort the list in descending order of Fabrication Net Value and it would start with the first row and continue adding each row. Here is the result I am looking for.

Machine Name	Fabrication Net Value	Running Total
Rollpacks	16752	16752
Tightwinder	12390	29142
T8 ( 2 )	10217	39359
T8 ( 4 )	5821	45180
Cut off saw	5801	50981
T8 ( 3 )	2509	53490
T8 (1 )	2155	55645
Tarp saw	568	56213
C52 Pillow Machine	309	56522
Bun Roller Machine	229	56751
Chinese Carousel	124	56875

Habib · ‎07-18-2016

To achieve this you need to add some order number to you machines for DAX to identify how it should calculate running total. In your example i have added SrNo column and gave sequence values to this. Next I added a new column with below formula and it gave me desired results....

Another idea other than SrNo is to use the row number based on sorted column of your choice

RunningTotal = CALCULATE(SUM(Machine[Fabrication Net Value]),all(Machine),Machine[SrNo]<=EARLIER(Machine[SrNo]))

Below is the output

joglidden · ‎07-18-2016

But what if the order changes, because of the magnitude of Fabrication Net Value? I.e., Tightwinder outperforms Rollpacks... maybe that will still work. The best example I've found so far is here:

http://www.daxpatterns.com/cumulative-total/

But the problem is that this example uses dates.

Habib · ‎07-18-2016

Hi @joglidden In your mentioned post, all calculations are based on date and its easy to calculate running totoal in that scenario.... In given scenario, where date is not available only option left is to use the some sequence number....

joglidden · ‎07-18-2016

You are probably right. I'm going to try anyway. But I think the solution will require an index, or row number (as an index), as you suggest.

jahida · ‎07-18-2016

You don't necessarily need an index, here's an example of a Measure that doesn't use one:

RunningTotal = MAXX(Table2, CALCULATE(SUM(Table2[Fabrication Net Value]), Table2[Fabrication Net Value] >= EARLIER(Table2[Fabrication Net Value]), ALL(Table2)))

As written right now, filters won't work. If you are using filters (on the columns not used in the calculations), change the ALL to ALLEXCEPT(Table2, Table2[FilteredColumn]...) and it should work. If you have filters on the columns in the calculation, things will get a bit more complicated with this method.