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

Thanks for your help, unfortunately I could not get any of these solutions to work.  Please remember I am using Direct Query so calculated columns is not an option.  I have however succeded in creating a measure to calculate the ranking for each machine so maybe I could use that in the calculation.  I created this one with dates and although it works does not serve for my needs:

 

CumulativeFabricationNetValueDate = CALCULATE ( SUM (vwScalesDataFabricationNetValue[Fabrication Net Value]), FILTER(ALL(vwScalesDataFabricationNetValue[WeightGeneralDate]), vwScalesDataFabricationNetValue[WeightGeneralDate] <= MAX(vwScalesDataFabricationNetValue[WeightGeneralDate])))

 

I tried doing something similar with the ranking measure instead of date but as yet no luck.