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

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.