Forum Discussion

kpickle's avatar
kpickle
Frequent Visitor
3 years ago
Solved

Running Total with nested calculation

Hi there, 

Is there a way to add a nested calculation within a running total? 

Example:

When the running total hits 1,000 subtract 900 and continue the running total...

Would it better to try this as a measure or a calculated column?

Thank you.

 

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    3 years ago

    kpickle 

    you can update the DAX like below

    Column = 
VAR _SUM=SUMX(FILTER('Table','Table'[date]<=EARLIER('Table'[date])),'Table'[Daily])
VAR _reach=int(_SUM/1000)
VAR _result=_SUM-_reach*900
return if (_result>1000,_result-900,_result)

6 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    3 years ago

    Hi kpickle 

     

    Yes.  Let's say you are calcuting the RT like this (a measure, this is better than a static calculated column)

     

     

    = CALCULATE(SUM([Actual]), FILTER(ALLSELECTED('Calendar'[Date]),ISONORAFTER('Calendar'[Date], MAX(Actual[Date]), DESC)))

     

     

     

    You can modify that to check the value of the running total (storing it in a Variable called _RT) and carry out whatever modification you want based on the value of _RT e.g.

     

     

    = VAR _RT = CALCULATE(SUM([Actual]), FILTER(ALLSELECTED('Calendar'[Date]),ISONORAFTER('Calendar'[Date], MAX(Actual[Date]), DESC)))

RETURN

IF(_RT > 1000, _RT - 1000, _RT)

     

     

     

    Regards

     

    Phil

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    3 years ago

    kpickle 

    agree with PhilipTreacy , measure is better.

    you can try his solution

    if you want to create a column, you can try this

    Column = 
VAR _SUM=SUMX(FILTER('Table','Table'[date]<=EARLIER('Table'[date])),'Table'[Daily])
VAR _reach=int(_SUM/1000)
return _SUM-_reach*900

    • kpickle's avatar
      kpickle
      Frequent Visitor
      3 years ago

      Hey Guys, 

      I tried it both ways and a couple issues - both work for the first trigger. 

       

      As a measure:

       

       

      Question 1 - is there any logic I can add behind the measure to calculate off the previous days result instead of SUM?

       

      As a calcualted column:

       

       

      Question 2 - Is there logic behind calculating the integer and rounding it up somehow? This seems like the more comlplex solution though.

      I need to take a mental break from this so here is some sample data if anyone wants to take a crack at it. Thank you!

       

      DateDailySUMreach_INTTest1 (column)Test2 (measure)CORRECTTriggerDecrease
      9/26/202210510501051051051000900
      9/27/20221002050205205205  
      9/28/20221253300330330330  
      9/29/20221504800480480480  
      9/30/20221155950595595595  
      10/1/20221607550755755755  
      10/2/20221108650865865865  
      10/3/202220010651165165165  
      10/4/202210011651265265265  
      10/5/202220513701470470470  
      10/6/202218015501650650650  
      10/7/202211016601760760760  
      10/8/202219018501950950950  
      10/9/2022170202022201120220  
      10/10/2022180220024001300400  
      10/11/2022195239525951495595  
      10/12/2022115251027101610710  
      10/13/2022115262528251725825  
      10/14/2022160278529851885985  
      10/15/20221102895210951995195  
      10/16/2022200309533952195395  
      10/17/2022100319534952295495  
      10/18/2022205340037002500700  
      10/19/2022180358038802680880  
      10/20/2022110369039902790990  
      10/21/20221903880311802980280  
      10/22/2022180406044603160460  
      10/23/2022110417045703270570  
      10/24/2022190436047603460760  
      10/25/2022170453049303630930  
      10/26/20221604690410903790190  
      10/27/20221104800412003900300  
      • ryan_mayu's avatar
        ryan_mayu
        Super User
        3 years ago

        kpickle 

        you can update the DAX like below

        Column = 
VAR _SUM=SUMX(FILTER('Table','Table'[date]<=EARLIER('Table'[date])),'Table'[Daily])
VAR _reach=int(_SUM/1000)
VAR _result=_SUM-_reach*900
return if (_result>1000,_result-900,_result)