If Negative Sum 0

[AllanBerces]

Hi good day, can anyone help me on my calculated column, from my table we required if the sum of total progress per Job is negative then the result is 0 if not then Progress.

DESIRED OUTPUT

Job	Trade	Year	Progress	Week No.	Result
KI222221	Mech	2026	9.19	3	9.19
KI222221	Mech	2026	1.81	4	1.81
KI222221	Mech	2026	15.68	2	15.68
KI222221	Mech	2026	14.6	1	14.6
WERT0123	E&I	2026	4.34	1	4.34
WERT0123	E&I	2026	5.52	2	5.52
WERT0123	E&I	2026	3.4	3	3.4
WERT0123	E&I	2026	69.27	4	69.27
QWERT65	Civil	2026	30.97	1	30.97
QWERT65	Civil	2026	6.51	2	6.51
QWERT65	Civil	2026	14.56	3	14.56
QWERT65	Civil	2026	17.7	4	17.7
BFGT435	Mech	2026	9.44	4	0
BFGT435	Mech	2026	-30.06	2	0
TYJJ56984	Plumber	2026	0.02	1	0
TYJJ56984	Plumber	2026	-9.47	2	0
TYJJ56984	Plumber	2026	-0.02	3	0
TYJJ56984	Plumber	2026	4.48	4	0
HOYTJ0978	Civil	2026	-4.06	2	0
HOYTJ0978	Civil	2026	19.73	4	0
HOYTJ0978	Civil	2026	-39.99	3	0

[rohit1991 · Accepted solution]

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

[ThxAlot · Accepted solution]

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

[cengizhanarslan · Accepted solution]

Please try the formula below:

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

[danextian · Accepted solution]

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
)

[Ashish_Mathur · Accepted solution]

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.

[danextian · Accepted solution]

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
)

[AllanBerces]

Hi @danextian @cengizhanarslan @ThxAlot @rohit1991 thank you very much for the reply working perfectly.

[cengizhanarslan · Accepted solution]

Please try the formula below:

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

[ThxAlot · Accepted solution]

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

[rohit1991 · Accepted solution]

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