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.

Lucy64's avatar
Lucy64
Advocate II
10 years ago

It is the total of several rows per machine.

jahida's avatar
jahida
Impactful Individual
10 years ago

If you have the ranking created already, you can probably just replace the Fabrication Net Value with Ranking everywhere but in the SUM, and it should work.

 

I could write a new measure but this seems like the easiest way.

 

MAXX(vwScalesDataFabricationNetValue, CALCULATE(SUM(vwScalesDataFabricationNetValue[Fabr​ication Net Value]), vwScalesDataFabricationNetValue[Ranking] >= EARLIER(vwScalesDataFabricationNetValue[Ranking]), ALL(vwScalesDataFabricationNetValue)))