SUMX of SUMMARIZE not showing correct total

[danextian]

Hello,

I'm stuck at this as the result is not what I have expected. Basically my goal here is to dynamically move certain balance sheet accounts between assets and liabilities group depending on the current balance. If an account has two balance sheet groups and the balance is positive then it is an asset else a liability. Once moved, I will then get the total of each group. On the account level, the formula is showing correctly but the balance sheet group total is overstated. 

Totals_ = 
VAR __groupcount =
    CALCULATE (
        DISTINCTCOUNT ( Accounts[Balance Sheet Group] ),
        ALLEXCEPT ( Accounts, Accounts[Balance Sheet Account Name] )
    )
RETURN
    SUMX (
        SUMMARIZE (
            Accounts,
            Accounts[Balance Sheet Account Name],
            Accounts[Balance Sheet Group],
            "Balance", SWITCH (
                TRUE (),
                Accounts[Balance Sheet Group] = BLANK() || Accounts[Balance Sheet Group] = "",  BLANK(),
                [Totals by Balance Sheet Account] > 0
                    && __groupcount > 1
                    && Accounts[Balance Sheet Group] = "Assets", [Totals by Balance Sheet Account],
                [Totals by Balance Sheet Account] < 0
                    && __groupcount > 1
                    && Accounts[Balance Sheet Group] = "Assets", BLANK (),
                [Totals by Balance Sheet Account] < 0
                    && __groupcount > 1
                    && Accounts[Balance Sheet Group] = "Liabilities", [Totals by Balance Sheet Account],
                [Totals by Balance Sheet Account] > 0
                    && __groupcount > 1
                    && Accounts[Balance Sheet Group] = "Liabilities", BLANK (),
                Accounts[Balance Sheet Group] = "Equity", [Totals (old)],
                [Totals (old)]
            )
        ),
        [Balance]
    )

 
In the below table, the total should be 883K for July but the total is showing 1.242M which is way off. Help would be much appreciated.

[danextian]

After checking, the formula appears to be ignoring the negative values when doing a sumx.

[danextian · Accepted solution]

So I ended up splitting those accounts that move between assets and liabilities by creating two variables. This is the updated formula:

Totals = 
VAR __ONE_GROUP =
    SUMX (
        ADDCOLUMNS (
            FILTER (
                SUMMARIZE (
                    Accounts,
                    Accounts[Balance Sheet Group],
                    Accounts[Balance Sheet Account Name]
                ),
                [Group Count by Balance Sheet Account Name] = 1
            ),
            "Balance", [Totals by Balance Sheet Account]
        ),
        [Balance]
    )
VAR __TWO_GROUPS =
    SUMX (
        ADDCOLUMNS (
            FILTER (
                SUMMARIZE (
                    Accounts,
                    Accounts[Balance Sheet Group],
                    Accounts[Balance Sheet Account Name]
                ),
                [Group Count by Balance Sheet Account Name] > 1
            ),
            "Balance", SWITCH (
                TRUE (),
                [Totals by Balance Sheet Account] > 0
                    && Accounts[Balance Sheet Group] = "Assets", [Totals by Balance Sheet Account],
                [Totals by Balance Sheet Account] < 0
                    && Accounts[Balance Sheet Group] = "Assets", BLANK (),
                [Totals by Balance Sheet Account] > 0
                    && Accounts[Balance Sheet Group] = "Liabilities", BLANK (),
                [Totals by Balance Sheet Account] < 0
                    && Accounts[Balance Sheet Group] = "Liabilities", [Totals by Balance Sheet Account],
                BLANK ()
            )
        ),
        [Balance]
    )
RETURN
    __TWO_GROUPS + __ONE_GROUP

 Sometimes DAX can be very tricky.