Forum Discussion
Matrix subtotals incorrect when using Measure
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
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.
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]))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.
