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

[kaniggit]

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.  😞

[Phil_Seamark · Accepted solution]

Hi @kaniggit

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

[Phil_Seamark · Accepted solution]

Hi @kaniggit

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

[kaniggit]

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"?

[kaniggit]

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"?

[kaniggit · Accepted solution]

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