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