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

Where would the running total start, and what would be the next value added to the total?

  • Lucy64's avatar
    Lucy64
    Advocate II
    10 years ago

    I would sort the list in descending order of Fabrication Net Value and it would start with the first row and continue adding each row.  Here is the result I am looking for.

     

    Machine NameFabrication Net ValueRunning Total
    Rollpacks1675216752
    Tightwinder1239029142
    T8 ( 2 )1021739359
    T8 ( 4 )582145180
    Cut off saw580150981
    T8 ( 3 )250953490
    T8 (1 )215555645
    Tarp saw56856213
    C52 Pillow Machine30956522
    Bun Roller Machine22956751
    Chinese Carousel12456875
    • Habib's avatar
      Habib
      Continued Contributor
      10 years ago

      To achieve this you need to add some order number to you machines for DAX to identify how it should calculate running total. In your example i have added SrNo column and gave sequence values to this. Next I added a new column with below formula and it gave me desired results....

       

      Another idea other than SrNo is to use the row number based on sorted column of your choice

       

      RunningTotal = CALCULATE(SUM(Machine[Fabrication Net Value]),all(Machine),Machine[SrNo]<=EARLIER(Machine[SrNo]))

       

      Below is the output

       

      • joglidden's avatar
        joglidden
        Advocate III
        10 years ago

        But what if the order changes, because of the magnitude of Fabrication Net Value? I.e., Tightwinder outperforms Rollpacks... maybe that will still work. The best example I've found so far is here: 

         

        http://www.daxpatterns.com/cumulative-total/

         

        But the problem is that this example uses dates.