Forum Discussion

mbg032373's avatar
mbg032373
Regular Visitor
5 years ago

Matrix subtotals incorrect when using Measure

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:

 

 

 

Need Help

8 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    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's avatar
      mbg032373
      Regular Visitor
      5 years ago

      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's avatar
        Greg_Deckler
        Community Champion
        5 years ago

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