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

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

Lucy64's avatar
Lucy64
Advocate II
10 years ago

Tried it but got an error :

 

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

 

The ranking is a measure not a calculated column, does that make a difference?  I have underlined the section highlighted for error.

 

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

 

  • 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

    Ok, yeah the fact that it's a measure does make a difference, but I should have guessed that, my bad. Anyway, try this:

     

    RunningTotal2 = MAXX(Table1, SUMX(
FILTER(
	SUMMARIZE(ALL(Table1), Table1[MachineName], "NetVal", SUM(Table1[Fabrication Net Value])),
	[NetVal] >=
		SUMX(FILTER(ALL(Table1), Table1[MachineName]= EARLIER(Table1[MachineName], 2)), Table1[Fabrication Net Value]))
, [NetVal]))

     

    Works on my local, although my testing data might not be perfect (obviously it wasn't the first time).

  • Lucy64's avatar
    Lucy64
    Advocate II
    10 years ago

    Thanks, well that worked for all the data, now what if I want to filter.  The Machines are alocated to Buildings so when I filter on building it does not recalc, is this where I need to use ALLEXCEPT instead of ALL? 

     

    By the way I am very greatful for your help.

  • 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

    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