Matrix subtotals incorrect when using Measure

[mbg032373]

I have tried several different solutions posted here and other places trying to get this to work out, but I seem to just not be getting it.  I'm trying to create a balance sheet, which I have mostly successfully accomplished; however, getting NET INCOME added at the bottom has been a challenge to say the least.  I have everything pretty much working; however, NET INCOME isn't getting included in any of the subtotals.

Here is the Measure (this will be for the middle column of numbers in the screenshot):

Actual MTD = 
   SWITCH(SELECTEDVALUE(HuntMainAccounts[MAINACCOUNTNAME]),
        "Net Income",CALCULATE(   
                        -SUMX(
                            SUMMARIZE(HuntLedgerTransactions,HuntMainAccounts[MAINACCOUNT],
                                "Balance",TOTALMTD(SUM(HuntLedgerTransactions[USD Amount]),HuntDate[DATEKEY])),
                            [Balance]
                        ),
                        HuntLedgerTransactions[Statement Type] = "Income Statement",REMOVEFILTERS(HuntMainAccounts)
                    ),
        SUMX(
            SUMMARIZE(HuntLedgerTransactions,HuntMainAccounts[MAINACCOUNT],
                "Balance",[GL Actual]),
            [Balance]
        )
    )

The measure generates all of the correct totals.  The value it calculates for "Net Income" is correct and gets added to the correct spot in the matrix.  For some reason, I just can't get it to be added to the subtotals.

I have also tried this combination of measure as well.  I get the same results as with the one above.  All totals are correct, Net Income is missing from subtotals.

Display Actual = SWITCH(SELECTEDVALUE(HuntMainAccounts[MAINACCOUNTNAME]),
                    "Net Income",-[Net Income],
                    [GL Actual]
)

GL Actual = 
    VAR _Balance = SUM(HuntLedgerTransactions[USD Amount])
    RETURN

    IF(ISBLANK(_Balance),0,_Balance)

Net Income = 
    CALCULATE(TOTALMTD(SUM(HuntLedgerTransactions[USD Amount]),HuntDate[DATEKEY]),
        HuntLedgerTransactions[Statement Type] = "Income Statement",REMOVEFILTERS(HuntMainAccounts)
    )

This is what the Totals SHOULD look like:

[Greg_Deckler]

@mbg032373 Hard to say with the information provided. Sample data and such would help. I'm not even sure which column is the one you are having issues with. That said, This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[mbg032373]

@Greg_Deckler Thanks for taking the time to respond.  I have checked out the posts you linked already.  I've thrown together some sample data and sample expected output to help explain this better.

Assume this is our data table (this is way over simplied, but I think it will get the main point across):

This would be our expected output:

So a couple of key points.  Notice that NET INCOME gets added to the bottom of the output matrix even though it doesn't exist in the source.  Every line above NET INCOME is a SUM(USD Amount) based on MAINACCOUNT.  NET INCOME is a SUM(ALL(USD Amount)) where Statement Type = "Income Statement".  SubTotals are broken out by the first digit of MAINACCOUNT.

Everything works properly with the exception of the final SubToal and the GrandTotal.  Neither of them include NET INCOME in their calculations.

I've seen other posts where they point to another site to create a formatted table with all of the accounts in it, then do totals and subtotals manually; however, in my case, this needs to work across 25 different legal entities all of which have different active MAINACCOUNTS.  Therefore, I can't preformat the output and link the values to the preformatted table.  It all needs to be done dynamically based on the legal entities account structure.

[Greg_Deckler]

@mbg032373 So based upon Measure Totals the Final Word, check out Measure and Measure Total in the attached PBIX file below sig.

Measure = 
    SWITCH(TRUE(),
        MAX([MAINACCOUNTNAME]) = "NET INCOME",SUMX(FILTER(ALL('Table2'),[Statement Type]="Income Statement"),[USD Amount]),
        SUMX(FILTER('Table2',[Statement Type]="Balance Sheet"),[USD Amount])
    )


Measure Total = 
    IF(HASONEVALUE(Table2[MAINACCOUNTNAME]),[Measure],
    SUMX(SUMMARIZE('Table2',[GROUP],[MAINACCOUNTNAME],"__Value",[Measure]),[__Value]))

[mbg032373]

@Greg_Deckler  Thanks again for the response and the assistance.  I've attempted something like this before and couldn't get Net Income to calculate, but gave your sample measure a shot. 

I've created the following measure based on your latest information:

Balance Sheet Actual = 
    SWITCH(TRUE(),
        MAX(HuntLedgerTransactions[MAINACCOUNTNAME]) = "Net Income",SUMX(FILTER(ALL(HuntLedgerTransactions),HuntLedgerTransactions[Statement Type] = "Income Statement"),HuntLedgerTransactions[USD Amount]),
        SUMX(FILTER(HuntLedgerTransactions,HuntLedgerTransactions[Statement Type] = "Balance Sheet"),HuntLedgerTransactions[USD Amount])
    )

Balance Sheet Total = 
    IF(HASONEVALUE(HuntLedgerTransactions[MAINACCOUNTNAME]),[Balance Sheet Actual],
        SUMX(SUMMARIZE(HuntLedgerTransactions,HuntLedgerTransactions[Account Type],HuntLedgerTransactions[MAINACCOUNTNAME],"_Value",[Balance Sheet Actual]),[_Value])
    ) 

NET INCOME doesn't end up getting calculated and grand totals end up being what subtotals should be.

[Greg_Deckler]

@mbg032373 Must be how your data is laid out. In your example data, I didn't see any logical reason why the net balance was associated with account D so I added that information into the model.

[mbg032373]

@Greg_Deckler I figured out why NET INCOME wasn't getting populated for me, so I think we are getting really close now.  Now I just need to figure out how to get NET INCOME actually calculated correctly using SUMX.

Here is the actual measure used to get NET INCOME:

Net Income = 
    CALCULATE(TOTALMTD(SUM(HuntLedgerTransactions[USD Amount]),HuntDate[DATEKEY]),
        HuntLedgerTransactions[Statement Type] = "Income Statement",REMOVEFILTERS(HuntMainAccounts)
    )

How would I accomplish the same results using SUMX?

[Greg_Deckler]

@mbg032373 Well, fundamentally, you would use HASONEVALUE and return that measure if true. If false, you would use SUMMARIZE or SUMMARIZECOLUMNS to group your items just like they are in the matrix visual and put this in a table VAR. You would use ADDCOLUMNS to add this measure as a calculated column within that table VAR and then you would SUMX across it.

[mbg032373]

@Greg_Deckler Just closing the loop on this one.  This ended up being the final measure used to get the correct results.  Thanks agains for the help.

Display Actual YTD = 
    VAR _DisplayActual = IF(MAX(HuntMainAccount[MAINACCOUNTCODE]) = "399999", -[Net Income YTD] + [Account Balance YTD], [Account Balance YTD])
    VAR _DisplayActual2 = IF(MAX(HuntMainAccount[MAINACCOUNTTYPE]) = "Equity" || MAX(HuntMainAccount[TYPETOTAL]) = "Total Liabilities & Net Worth", -[Net Income YTD] + [Account Balance YTD],[Account Balance YTD])
    VAR _Result = IF(HASONEFILTER(HuntMainAccount[MAINACCOUNTTYPE]), _DisplayActual, _DisplayActual2)
    RETURN 
    
    ROUND(_Result,2)