cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
danextian New Contributor
New Contributor

SUMX of SUMMARIZE not showing correct total

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.
2019-11-04 15_30_56-Window.png

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
1 ACCEPTED SOLUTION

Accepted Solutions
danextian New Contributor
New Contributor

Re: SUMX of SUMMARIZE not showing correct total

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. 

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

2 REPLIES 2
danextian New Contributor
New Contributor

Re: SUMX of SUMMARIZE not showing correct total

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

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
danextian New Contributor
New Contributor

Re: SUMX of SUMMARIZE not showing correct total

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. 

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,241)