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