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

Actually I noticed something very interesting, look at this:

 

 

Although the running total does not recalculate when I show as a percentage it does recalc on filter,  the % is what I am after so I believe this solution will work for me.

jahida's avatar
jahida
Impactful Individual
10 years ago

Hm, ok. I'm trying to work through a solution that allows filtering, since ALLEXCEPT only plays nice when it's in a CALCULATE statement, but if that works for you then great. Still going to try to finish if only for the intellectual value.

  • jahida's avatar
    jahida
    Impactful Individual
    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

    One more note, the results you listed above as the solution are not correct. They're a weird % of the largest number in the grand total column, which isn't what you want I think. Make sure you switch to the more recent formula I provided.

     

    Sorry for the spam.

  • Lucy64's avatar
    Lucy64
    Advocate II
    10 years ago

    I cannot get the last version to work,  this is what I wrote:

     

    RunningTotal2 = MAXX(vwScalesDataFabricationNetValue, SUMX(

    FILTER(

       SUMMARIZE(CALCULATETABLE(vwScalesDataFabricationNetValue, ALLEXCEPT(vwScalesDataFabricationNetValue, vwScalesDataFabricationNetValue[BuildingName]), ALLSELECTED(vwScalesDataFabricationNetValue[BuildingName])), vwScalesDataFabricationNetValue[MachineName], "NetVal", SUM(vwScalesDataFabricationNetValue[Fabrication Net Value])),

       [NetVal] >=

           SUMX(FILTER(CALCULATETABLE(vwScalesDataFabricationNetValue, ALLEXCEPT(vwScalesDataFabricationNetValue, vwScalesDataFabricationNetValue[BuildingName]), ALLSELECTED((vwScalesDataFabricationNetValue[BuildingName])), vwScalesDataFabricationNetValue[MachineName]= EARLIER(vwScalesDataFabricationNetValue[MachineName], 2)), vwScalesDataFabricationNetValue[Fabrication Net Value])), [NetVal])))

     

    The error message I got was "Too few arguments were passed to the SUMX function. The minimum argument count for the function is 2." and I have put in red the sections that were highlighted.

     

  • jahida's avatar
    jahida
    Impactful Individual
    10 years ago
    Lucy64 wrote:

    @I cannot get the last version to work,  this is what I wrote:

     

    RunningTotal2 = MAXX(vwScalesDataFabricationNetValue, SUMX(

    FILTER(

       SUMMARIZE(CALCULATETABLE(vwScalesDataFabricationNetValue, ALLEXCEPT(vwScalesDataFabricationNetValue, vwScalesDataFabricationNetValue[BuildingName]), ALLSELECTED(vwScalesDataFabricationNetValue[BuildingName])), vwScalesDataFabricationNetValue[MachineName], "NetVal", SUM(vwScalesDataFabricationNetValue[Fabrication Net Value])),

       [NetVal] >=

           SUMX(FILTER(CALCULATETABLE(vwScalesDataFabricationNetValue, ALLEXCEPT(vwScalesDataFabricationNetValue, vwScalesDataFabricationNetValue[BuildingName]), ALLSELECTED((vwScalesDataFabricationNetValue[BuildingName])), vwScalesDataFabricationNetValue[MachineName]= EARLIER(vwScalesDataFabricationNetValue[MachineName], 2)), vwScalesDataFabricationNetValue[Fabrication Net Value])), [NetVal])))

     

    The error message I got was "Too few arguments were passed to the SUMX function. The minimum argument count for the function is 2." and I have put in red the sections that were highlighted.

     

    Remove the two brackets in red. They weren't in the initial formula

  • Lucy64's avatar
    Lucy64
    Advocate II
    10 years ago

    Thanks, as you can see I am new to DAX, I really need a crash course!  This one did the trick.   Great job!

  • Lucy64's avatar
    Lucy64
    Advocate II
    10 years ago

    So now we are adding a hierarchy for Plant, Building, Production Line, Machine, how do I incorporate this into the query so that the figures recalc as I drill down and drill up.

     

    I though I had posted this yesterday but did not see it today so sorry if I am repeating.

  • Nachospt's avatar
    Nachospt
    Frequent Visitor
    10 years ago

    There is a MAXX function there. POWER BI tells me that kind of functions cant be used in calculated columns in DIRECT QUERY mode.

    What am i missing?

  • jahida's avatar
    jahida
    Impactful Individual
    10 years ago

    It should be a Measure, not a Column.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    thanks for this! jahida