Dealing with Negative numbers in Grand Total with IF statement

[PauloRicardo]

Hi guys,

I haven't found any answer related to my problem, that's why i'm oppening this topic.

First off, let me show the DAX measure:

MEASURE =

VAR POSITIVE =

    CALCULATE (

        SUMX ( FICHA_FINANCEIRA, [Sum Events] ),

        FILTER (

            VALUES ( FICHA_FINANCEIRA[COD_EVENTS] ),

            FICHA_FINANCEIRA[COD_EVENTS]

            IN {260, 261}

        )

    )

VAR NEGATIVE =

    CALCULATE (

        SUMX ( FICHA_FINANCEIRA, [Sum Events] ),

        FILTER (

            VALUES ( FICHA_FINANCEIRA[COD_EVENTS] ),

            FICHA_FINANCEIRA[COD_EVENTS] IN { 730 }

        )

    )

VAR SUMM = POSITIVE - NEGATIVE

RETURN

IF(SUMM < 0, 0, SUMM)

I'm struggling with the grand total Result. It sum the "Positive" values and then subtract with the "Negative" values. That's right for the rows. But with the Grand total, instead, my need is to sum the "positive" values and when the value is "Negative", it goes as 0.

I think that the problem is in the IF condition, but i'm not sure, because for what i know, the grand total is not the sum of the rows, but it's a different calculation based on agreggations. Still, i don't know how to implement a calculation that does the sum. Maybe with a virtual table it can work? But how?!

See how the results are being returned and how it should be returning:

REFERENCE	VALUE	MEASURE
Row	5490,31	This value resulting is above 0, so it shows the sum of the values.
Row	0,00	The first IF condition is met, where the value resulting is negative (442,12 - (-2295,46) = -1853,34), so it returns 0.
WRONG GRAND TOTAL	3636,97	Returns the result of 5490,31 + 442,12 - (-2295,46) = 3636,97. Wrong.
RIGHT GRAND TOTAL	5490,31	The expected result. It should return the sum of 5490,31 - 0 = 5490,31. Right.

There's a way to do it without creating a calculated column or altering the data?

Welp 😔

[PauloRicardo · Accepted solution]

*UPDATE*

I found a way to return the expected result.

I had to create 4 measures:
-The values that i want to sum
-The values i want to subtract
-Another measure with the same measure that the sum and the subtract as variables and do the positive minus the negative
-A final measure that uses the IF statement to verify the conditions.

The first and the second measure is the same, altering only the Filtering events.

RESCISÃO (ADIÇÃO) / RESCISÃO (SUBTRAÇÃO) = CALCULATE (

        SUMX ( FICHA_FINANCEIRA, [Sum Events] ),

        FILTER (

            VALUES ( FICHA_FINANCEIRA[COD_EVENTO] ),

            FICHA_FINANCEIRA[COD_EVENTO]

                IN {

                    260,

                }

        )

    )

The third measure is the exact same as the first two, but you put the same DAX measure in variables, create a third variable summing the positive and negative values.

VAR POSITIVO =

    CALCULATE (

        SUMX ( FICHA_FINANCEIRA, [Soma Eventos] ),

        FILTER (

            VALUES ( FICHA_FINANCEIRA[COD_EVENTO] ),

            FICHA_FINANCEIRA[COD_EVENTO]

                IN { 260 }

        )

    )

VAR NEGATIVO =

    CALCULATE (

        SUMX ( FICHA_FINANCEIRA, [Soma Eventos] ),

        FILTER (

            VALUES ( FICHA_FINANCEIRA[COD_EVENTO] ),

            FICHA_FINANCEIRA[COD_EVENTO] IN { 720 }

        )

    )

VAR SUMM = POSITIVO - NEGATIVO

RETURN

SUMM

Then, the fourth measure is the condition that result in the expected result:

RESCISÃO =

    SUMX(

        SUMMARIZE(

        COLABORADORES,

        COLABORADORES[NOME_COLABORADOR]),

        IF([RESCISAO (SOMA)] < 0, [RESCISÃO (ADIÇÃO)] - [RESCISÃO (ADIÇÃO)],

            IF([RESCISAO (SOMA)] > 0, [RESCISÃO (ADIÇÃO)] - [RESCISÃO (SUBTRAÇÃO)]

            )

        )

    )


Hope it helps someone else.

[v-kongfanf-msft]

Hi @PauloRicardo ,

Thank you for your feedback. If the problem has been resolved, please mark it as the correct solution, and point out if the problem persists.

Best Regards,
Adamk Kong

[v-kongfanf-msft]

Hi @PauloRicardo ,

Maybe you can try formula like below to create measure:

measure_ = 
VAR POSITIVE =
    CALCULATE (
        SUMX ( 'Table', [Sum Events] ),
        FILTER (
            VALUES ( 'Table'[COD_EVENTS] ),
            'Table'[COD_EVENTS]
            IN {260, 261}
        )
    )
VAR NEGATIVE =
    CALCULATE (
        SUMX ( 'Table', [Sum Events] ),
        FILTER (
            VALUES ( 'Table'[COD_EVENTS] ),
            'Table'[COD_EVENTS] IN { 730 }
        )
    )
VAR SUMM = POSITIVE - NEGATIVE
VAR RESULT = IF(SUMM < 0, 0, SUMM)

VAR _Table =
    SUMMARIZE(
        'Table',
        'Table'[REFERENCE],
        "Positives", CALCULATE(SUMX('Table', [Sum Events]), 'Table'[COD_EVENTS] IN {260, 261}),
        "Negatives", CALCULATE(SUMX('Table', [Sum Events]), 'Table'[COD_EVENTS] IN {730})
    )

VAR _AdjustedTotal =
    SUMX(
        _Table,
        IF([Positives] - [Negatives] < 0, 0, [Positives] - [Negatives])
    )

RETURN
IF(HASONEVALUE('Table'[REFERENCE]), RESULT, _AdjustedTotal)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.