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

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.

mbg032373's avatar
mbg032373
Regular Visitor
5 years ago

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)