Forum Discussion

Lucy64's avatar
Lucy64
Advocate II
10 years ago
Solved

Running Total

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...
Need Help
  • jahida's avatar
    jahida
    10 years ago

    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.

joglidden's avatar
joglidden
Advocate III
10 years ago

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's avatar
jahida
Impactful Individual
10 years ago

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.