Forum Discussion

abssenthil_pbi's avatar
abssenthil_pbi
Frequent Visitor
4 years ago
Solved

Running total by ignoring negative values

Hi All,

I am looking for a help where i need to calculate the running total by ignoring the negative values

below is the sample where i need to calculate C by adding A and only positive numbers of B 

C = SUM(Table[A])+IF(SUM(Table[B)>0,SUM(Table[B]),0)  works fine

and then calculate running total of C but its not working as expected for ex on Jul-18 i should get 600664 instead 600000

C Cumulative = CALCULATE(SUM(Table[A])+IF(SUM(Table[B)>0,SUM(Table[B]),0),filter(ALL(Table),Table[Month]<=MAX(Table[Month])))

tried C Cumulative = CALCULATE([C],filter(ALL(Table),Table[Month]<=MAX(Table[Month])))

or 

C Cumulative = CALCULATE([C],filter(ALL(Dates),Dates[Date]<=MAX(Dates[Date])))

 

can someone help me with this?

MonthABCC Cumulative
Jan-17  00
Feb-17 000
Mar-17 000
Apr-17 000
May-17 -463700
Jun-173000000300000300000
Jul-17 00300000
Aug-17 00300000
Sep-17 00300000
Oct-17 00300000
Nov-17 -2530300000
Dec-17 00300000
Jan-18 -370300000
Feb-18 00300000
Mar-18 00300000
Apr-18 00300000
May-18 -10300000
Jun-18 -20300000
Jul-18300000664300664600000
Aug-18 530530600000
Sep-18 2784227842624107
Oct-183000042430424654531
Nov-18 -58920648639
Dec-1812000012000660639
Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

    cumul =
    VAR d =    MAX ( 'Table'[Month] )
    VAR t =
        SUMMARIZE (
            FILTER ( ALLSELECTED ( 'Table' ), [Month] <= d ),
            'Table'[Month],
            "a", SUM ( 'Table'[A] ),
            "b", SUM ( 'Table'[B] )
        )
    RETURN    0  + SUMX ( t, [a] + MAX ( 0, [b] ) )

     

     

     

     

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    Here is one version using CALCULATE

     

    cumul = 
var d = max('Table'[Month])
return calculate(sum('Table'[A]),ALLSELECTED('Table'),'Table'[Month]<=d)+calculate(sum('Table'[B]),ALLSELECTED('Table'),'Table'[Month]<=d,'Table'[B]>0)

    Here is another one using SUMX

    cumul2 = 
var d = max('Table'[Month])
return SUMX(FILTER(ALLSELECTED('Table'),'Table'[Month]<=d),[A]+max([B],0))
    • abssenthil_pbi's avatar
      abssenthil_pbi
      Frequent Visitor
      4 years ago

      Hi lbendlin Thanks for the response. Its better than the old calc but not working as expected since we have more details at the table. Just to simplify i had given the summarized values at month level but the actual table with direct query has few other dimensions which breaks one row in to 1000's of rows. 

      The filter 'Table'[B]>0 applies at row level and it considers all positive adjusted rows in that month but the expectation is to summarize the adjusted value of that month and if that is>0 then consider.

      In the below example Nov-18 has one +ve row with 507 and the overall adjusted for that month is -5892. The provided formula just adds 507 to the prev month value in the running total.

      MonthABCC Cumulative 
      Jan-17  0  
      Feb-17 000 
      Mar-17 000 
      Apr-17 000 
      May-17 -463700 
      Jun-173000000300000300000 
      Jul-17 00300000 
      Aug-17 00300000 
      Sep-17 00300000 
      Oct-17 00300000 
      Nov-17 -2530300000 
      Dec-17 00300000 
      Jan-18 -370300000 
      Feb-18 00300000 
      Mar-1824299024299324299 
      Apr-18 00324299 
      May-18 -10324299 
      Jun-18 -20324299 
      Jul-18300000664300664624963 
      Aug-18 530530625494 
      Sep-188160027842109442734936 
      Oct-183000042430424765360 
      Nov-18 -58920765867507
      Dec-1812000-6812000777867 
      Jan-19 11777871 
      • lbendlin's avatar
        lbendlin
        Super User
        4 years ago

        Please provide sanitized sample data that fully covers your issue.

        Please show the expected outcome based on the sample data you provided.

  • abssenthil_pbi's avatar
    abssenthil_pbi
    Frequent Visitor
    4 years ago

    Thanks again. Hope the below information is clear

    My direct table looks like below

    MonthTypeAB
    Jan-17Org  
    Feb-17Org 0
    Mar-17Org 0
    Apr-17Org 0
    May-17Org -4637
    Jun-17Org3000000
    Jul-17Org 0
    Aug-17Org 0
    Sep-17Org 0
    Oct-17Org 0
    Nov-17Org -253
    Dec-17Org 0
    Jan-18Org -37
    Feb-18Org 0
    Mar-18Org 0
    Apr-18Org 0
    May-18Org -1
    Jun-18Org -2
    Jul-18Org300000664
    Aug-18Org 530
    Sep-18Org 27842
    Oct-18Org30000424
    Nov-18Org -6400
    Nov-18Adj 507
    Dec-18Org120000
    Dec-18Adj 9
    Dec-18Adj1 -5

    My calculated column C & Cumulative should be like this

    C=(A+ only +ve values of B)

    MonthABC=(A+only +ve of B)C Cumulative
    Jan-17  0 
    Feb-17 00 
    Mar-17 00 
    Apr-17 00 
    May-17 -46370 
    Jun-173000000300000300000
    Jul-17 00300000
    Aug-17 00300000
    Sep-17 00300000
    Oct-17 00300000
    Nov-17 -2530300000
    Dec-17 00300000
    Jan-18 -370300000
    Feb-18 00300000
    Mar-18 00300000
    Apr-18 00300000
    May-18 -10300000
    Jun-18 -20300000
    Jul-18300000664300664600664
    Aug-18 530530601194
    Sep-18 2784227842629036
    Oct-183000042430424659460
    Nov-18 -58930659460
    Dec-1812000412004671464

    My final output should be

     

    • lbendlin's avatar
      lbendlin
      Super User
      4 years ago

      cumul =
      VAR d =    MAX ( 'Table'[Month] )
      VAR t =
          SUMMARIZE (
              FILTER ( ALLSELECTED ( 'Table' ), [Month] <= d ),
              'Table'[Month],
              "a", SUM ( 'Table'[A] ),
              "b", SUM ( 'Table'[B] )
          )
      RETURN    0  + SUMX ( t, [a] + MAX ( 0, [b] ) )