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.

 

 

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.