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.

jahida's avatar
jahida
Impactful Individual
10 years ago

Did you try the measure I included earlier, and if so what was the result?

Lucy64's avatar
Lucy64
Advocate II
10 years ago

Here is the measure I used and the results:

 

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

 

RankingMachineNameFabrication Net ValueRunningTotal
1Quarrata2100374934
2Rollpacks1675287249
3Tightwinder1239078010
4T8 ( 2 )1021787114
5T8 ( 4 )582188776
6Cut off saw580189000
7OEM VCC's513388822
8T8 ( 3 )250985088
9T8 (1 )215576690
10Tarp saw190688949
11Mattress143579222
12OEM CNC112689020
13T8 ( 5 )88988625
14Fillmatic72435792
15Quality Samples49785254
16C52 Pillow Machine30988977
17Bun Roller Machine22962330
18Chinese Carousel12478134
  • jahida's avatar
    jahida
    Impactful Individual
    10 years ago

    Is each row of the table showing one row in the source data, or a number of rows in the source data?

     

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

    • 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)))