cancel
Showing results for
Did you mean:
Highlighted
danextian 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. "Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
1 ACCEPTED SOLUTION

Accepted Solutions
danextian 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 (
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 (
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."
2 REPLIES 2
danextian 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."
danextian 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 (
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 (
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."

Announcements   