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