cancel
Showing results for
Did you mean:
Highlighted
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
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
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."
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

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

#### 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

Find out where you can attend!

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