Forum Discussion

Zidane22's avatar
Zidane22
Icon for Helper II rankHelper II
3 years ago
Solved

Add and subtract from Running total

Hello everyone, 

 

Using the foruma below I get the running total, which is great but I dont want it to keep adding to itself if there is no value (column1) to be added. I also need it to subtract (column2) if there is a value. 

 

 

For example :

 

Column 1.    Column 2    Measure I need displayed: 

 

0.          -           0                  0 

7          -           0                   7 

26       -            5                   28 -- ( 7+ 26 = 33 -5 =28) 

0.        -            3                    25 ( 28+0 = 28 -3 =25)

 

 

Any help to achive this would mean so much, I've been suck on this for days! 

 

 

 

 

 

 

Running Total MEASURE = CALCULATE ( SUM ( 'All Web Site Data (2)'[UniquePageviews] ), FILTER ( ALL ( 'All Web Site Data (2)' ), 'All Web Site Data (2)'[Date] <= MAX ( 'All Web Site Data (2)'[Date] ) ) )

DAX
  • tamerj1's avatar
    tamerj1
    3 years ago

    Zidane22 

    Try

    Calculation =
VAR T =
    ALL ( 'All Web Site Data (2)' )
VAR T1 =
    TOPN ( 4, T, 'All Web Site Data (2)'[TIMES], ASC )
VAR StartTime =
    MAXX ( T1, 'All Web Site Data (2)'[TIMES] )
VAR InService =
    MAXX ( T, 'All Web Site Data (2)'[EBS IN SERVICE] )
VAR Result =
    SUMX (
        FILTER (
            T,
            'All Web Site Data (2)'[Date] <= MAX ( 'All Web Site Data (2)'[Date] )
        ),
        IF (
            'All Web Site Data (2)'[TIMES] > StartTime,
            'All Web Site Data (2)'[Column1] - 'All Web Site Data (2)'[Column2]
        )
    )
RETURN
    InService - Result
  • tamerj1's avatar
    tamerj1
    3 years ago

    Zidane22 


    VAR InService =
    MAXX (
    TOPN (
    1,
    TOPN ( 3, T, 'All Web Site Data (2)'[TIMES], ASC ),
    'All Web Site Data (2)'[TIMES]
    ),
    'All Web Site Data (2)'[EBS IN SERVICE]
    )

34 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Zidane22 

    please try

    RunningTotal =
    SUMX (
    FILTER (
    ALL ( 'All Web Site Data (2)' ),
    'All Web Site Data (2)'[Date] <= MAX ( 'All Web Site Data (2)'[Date] )
    ),
    'All Web Site Data (2)'[Column1] - 'All Web Site Data (2)'[Column2]
    )

     

    perhaps Greg_Deckler can provide you with a solution based on CALCULATE 😜

    • Zidane22's avatar
      Zidane22
      Icon for Helper II rankHelper II
      3 years ago

      Thank you tamerj1 , 

       

      It looks like its working I just have to check the math.

       

      Is their away to start the value of this calcuation of a from 0? have a differnt calculation that starts with the differnt column first value? So if the value is 90 in the in first item then it would start at 90 then go backwords into the negtives when it passes 1.  2 two measures . One starts a 0 (this one) and second one starts with the first value in a differnt column (90) for example. so I would have two lines 

       

      This calculation is used in line graph give the total after the light purple - oranage, give its total and carrying that total to the next line. Hope that makes sense.

       

       

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        Zidane22 

        Please provide a sample along with at least one example 

  • Zidane22's avatar
    Zidane22
    Icon for Helper II rankHelper II
    3 years ago

    Hello tamerj1 , 

     

    Please see exampe below. What i need t calcualute is the two columns in the yellow in two differnt measures. 

     

    Thank you so much for your help, really means alot. I've been really strugling with this.

     

     

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      3 years ago

      Zidane22 
      Taken from another column in the same table? Is it the sum of that column or the first value or what exactly? I noticed that sonetimes you are subtracting the TRUCKS AVILABLE and sometimes not, please clarify.

      • Zidane22's avatar
        Zidane22
        Icon for Helper II rankHelper II
        3 years ago

        Sorry Im not being clear, Its the same Table but differnt column name "EBS IN SERVICE" the first item will have the value that i need not the sum.  Yes only subtracting if trucks are in use.  These two calcuations basically need to tell me two things. 1) how many trucks are being used per hour (which you get from "TRUCKS REQ" subtracting "TRUCKS AVAILABLE" which is trucks coming back being retuned.  2) I only have certian amount of trucks in service, which the value dose change automaticaly and this is were the (90) for example comes in. Bascially in this calculation it will forcast if we will have enough trucks by mid day or we would be short beause its subtracting the amount of trucks that we need from what's actually in service. Hope this makes it more clear. Thank you again so much.