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

     

     

     

     

lbendlin's avatar
lbendlin
Icon for Super User rankSuper 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
    Icon for Super User rankSuper 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.