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 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]))
mbg032373's avatar
mbg032373
Regular Visitor
5 years ago

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.