If Negative Sum 0

Hii AllanBerces 

 

If the total Progress per Job (and Year) is negative, return 0, otherwise return the original Progress value.

Result = 
VAR TotalJobProgress =
    CALCULATE (
        SUM ( 'ProgressData'[Progress] ),
        ALLEXCEPT ( 'ProgressData', 'ProgressData'[Job], 'ProgressData'[Year] )
    )
RETURN
IF ( TotalJobProgress < 0, 0, 'ProgressData'[Progress] )

 

 

Result = 
IF(
    CALCULATE(
        ISEMPTY( DATA ), ALLEXCEPT( DATA, DATA[Job] ), DATA[Progress] < 0
    ),
    DATA[Progress],
    0
)

Please try the formula below:

Result =
VAR JobTotalProgress =
    CALCULATE (
        SUM ( 'Table'[Progress] ),
        ALLEXCEPT ( 'Table', 'Table'[Job] )
    )
RETURN
IF (
    JobTotalProgress < 0,
    0,
    'Table'[Progress]
)

Hi AllanBerces 

 

You can use MAX to make sure that it returns either the positive result or zero at the minimum.

Progress Per Job =
MAX (
    CALCULATE ( SUM ( 'Table'[Progress] ), ALLEXCEPT ( 'Table', 'Table'[Job] ) ),
    0
)

 

Hi,

This calculated column formula works

=if(CALCULATE(SUM(Data[Progress]),FILTER(Data,Data[Job]=EARLIER(Data[Job])))<0,0,Data[Progress])

Hope this helps.