Formula working but Grand Total not Summing

[Anonymous]

I have the following formula that is displaying the right numbers but not summing together in the table for the grand total corrrectly.  How can I fix ? Total should be way higher than 18.641m

Projected = IF(Calculate( SUMX('6. Actuals','6. Actuals'[Actuals]),'6. Actuals'[Actuals],'Calendar'[Year] = 2022)*-1 = Blank(),Calculate( SUMX('6. Actuals','6. Actuals'[Actuals]),'6. Actuals'[Actuals],'Calendar'[Year] = 2021)*-1,Calculate( SUMX('6. Actuals','6. Actuals'[Actuals]),'6. Actuals'[Actuals],'Calendar'[Year] = 2022)*-1)

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can try the following formula.

Projected = 
Var N1=IF (
    CALCULATE (
        SUMX ( '6. Actuals', '6. Actuals'[Actuals] ),
        '6. Actuals'[Actuals],
        'Calendar'[Year] = 2022
    ) * -1
        = BLANK (),
    CALCULATE (
        SUMX ( '6. Actuals', '6. Actuals'[Actuals] ),
        '6. Actuals'[Actuals],
        'Calendar'[Year] = 2021
    ) * -1,
    CALCULATE (
        SUMX ( '6. Actuals', '6. Actuals'[Actuals] ),
        '6. Actuals'[Actuals],
        'Calendar'[Year] = 2022
    ) * -1
)
Return
IF(HASONEVALUE('Calendar'[Month name]),N1,SUMX(VALUES('Calendar'[Month name]),N1))

Best Regards,

Community Support Team _Charlotte

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

[tamerj1]

Hi @Anonymous 

please try

Projected =
SUMX (
    VALUES ( 'Date'[Month Name] ),
    IF (
        CALCULATE (
            SUMX ( '6. Actuals', '6. Actuals'[Actuals] ),
            '6. Actuals'[Actuals],
            'Calendar'[Year] = 2022
        ) * -1
            = BLANK (),
        CALCULATE (
            SUMX ( '6. Actuals', '6. Actuals'[Actuals] ),
            '6. Actuals'[Actuals],
            'Calendar'[Year] = 2021
        ) * -1,
        CALCULATE (
            SUMX ( '6. Actuals', '6. Actuals'[Actuals] ),
            '6. Actuals'[Actuals],
            'Calendar'[Year] = 2022
        ) * -1
    )
)

[mahoneypat]

FYI that You should consider using variables to simplify your code and improve performance (your current measure would do the same query twice is the false path). Here is an example.

Projected =
VAR CY =
CALCULATE (
SUMX ( '6. Actuals', '6. Actuals'[Actuals] ),
'6. Actuals'[Actuals],
'Calendar'[Year] = 2022
) * -1
RETURN
IF (
ISBLANK ( CY ),
CALCULATE (
SUMX ( '6. Actuals', '6. Actuals'[Actuals] ),
'6. Actuals'[Actuals],
'Calendar'[Year] = 2021
) * -1,
CY
)

Also, to get the expected value in the total row, please follow the approach described in this video.

Power BI - Tales from the front #01 - Getting the Right Total - YouTube

Pat