cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## How do I make my Group total match the sum of the subtotals?

3 Tables - Acct, Shares, Certificates.

Total Deposits = sum(Share Balance) + sum(CertBal)

I want to display only those where "Total Deposits > 500,000" as "uninsured."  The problem is that the Total of the "accounts" doesn't match the total of the groups.  I have tried "calculate - filter" and various "sumx" but I have the same problem where the Total of the Group doesn't equal the group total.  😞

2 ACCEPTED SOLUTIONS

Accepted Solutions
MVP

## Re: How do I make my Group total match the sum of the subtotals?

Something like this pattern will be close.  I have attached a PBIX file for you to have a play with.

```Uninsured =

IF(
ISFILTERED('Table1'[Sub Group]),
-- Then --
IF([Total Deposits]>=500,[Total Deposits],0),
-- Else --
SUMX(
FILTER(
ALL(Table1[Group],Table1[Sub Group]),
'Table1'[Group] = MAX('Table1'[Group])
),
IF([Total Deposits]>=500,[Total Deposits],0)
)
)```

Proud to be a Datanaut!

Frequent Visitor

## Re: How do I make my Group total match the sum of the subtotals?

From Phil (above)

```Uninsured =
SWITCH(TRUE() ,
-- Handle the Acct lines
ISFILTERED('Acct'[Acct]) , IF([Total_Deposits]>500000,[Total_Deposits]-500000,0) ,
-- Handle the Group
sumx(
FILTER(
ALL(Acct[Group],Acct[Acct]),
Acct[Group] = MAX(Acct[Group])
),
IF([Total_Deposits]>=500000,[Total_Deposits]-500000,0)
)
)```

This worked for me!!

4 REPLIES 4
MVP

## Re: How do I make my Group total match the sum of the subtotals?

Something like this pattern will be close.  I have attached a PBIX file for you to have a play with.

```Uninsured =

IF(
ISFILTERED('Table1'[Sub Group]),
-- Then --
IF([Total Deposits]>=500,[Total Deposits],0),
-- Else --
SUMX(
FILTER(
ALL(Table1[Group],Table1[Sub Group]),
'Table1'[Group] = MAX('Table1'[Group])
),
IF([Total Deposits]>=500,[Total Deposits],0)
)
)```

Proud to be a Datanaut!

Frequent Visitor

## Re: How do I make my Group total match the sum of the subtotals?

I did that, but it still doesn't quite seem to work.?  I think this has everything to do with "row context"? I'm still new to all of this.

Here is my formula and the result... The "Group A" total would be correct (\$800-\$500 = \$300), but it is the sub-group (Account) totals that I want to "rollup" to the Group.  Maybe I screwed something up by using the "[Total Deposits]-500000"?

Frequent Visitor

## Re: How do I make my Group total match the sum of the subtotals?

Also, the formula for "Total_Deposits" is:
Total_Deposits = calculate(sum(Share[Share Balance]) + sum(Cert[CertBal]))

Should I change that to some SUMX or something with "RELATEDTABLE"?

Frequent Visitor

## Re: How do I make my Group total match the sum of the subtotals?

From Phil (above)

```Uninsured =
SWITCH(TRUE() ,
-- Handle the Acct lines
ISFILTERED('Acct'[Acct]) , IF([Total_Deposits]>500000,[Total_Deposits]-500000,0) ,
-- Handle the Group
sumx(
FILTER(
ALL(Acct[Group],Acct[Acct]),
Acct[Group] = MAX(Acct[Group])
),
IF([Total_Deposits]>=500000,[Total_Deposits]-500000,0)
)
)```

This worked for me!!

Announcements