Forum Discussion

ak77's avatar
ak77
Post Patron
2 years ago
Solved

Sub Total calculation Help

Hi All,   Please help for the below requirement for matrix Viz. I have the below Main Matrix table.. The user wants the MAIN TOTAL value to be only the sum of values where FundCert value is blank.....
Need Help
Show and Tell
  • parry2k's avatar
    parry2k
    2 years ago

    ak77 try this measure:

     

    Sum Value = SUM ( Sheet1[Values] )

Sum for Display = 
 SWITCH ( 
    TRUE (),
    HASONEVALUE ( Sheet1[Classification] ), CALCULATE ( [Sum Value], KEEPFILTERS ( NOT ISBLANK ( Sheet1[Fund Cert] ) ) ),
    CALCULATE ( [Sum Value], ISBLANK ( Sheet1[Classification] ) )
    
)

     

     

  • parry2k's avatar
    parry2k
    2 years ago

    ak77 the reason it is not working, value in the columns is not blank it is a string with no value, these are two different things:

     

    here is the updated measure:

     

    Sum Value 1 for Display = 
 SWITCH ( 
    TRUE (),
    HASONEVALUE ( Earningssample[Category_Classification] ), CALCULATE ( [Sum Value1], KEEPFILTERS ( COALESCE ( Earningssample[Category], "" ) <> "" ) ),
    CALCULATE ( [Sum Value1], COALESCE ( Earningssample[Category], "" ) = "" ) 
   )

     

parry2k's avatar
parry2k
Super User
2 years ago

ak77 the reason it is not working, value in the columns is not blank it is a string with no value, these are two different things:

 

here is the updated measure:

 

Sum Value 1 for Display = 
 SWITCH ( 
    TRUE (),
    HASONEVALUE ( Earningssample[Category_Classification] ), CALCULATE ( [Sum Value1], KEEPFILTERS ( COALESCE ( Earningssample[Category], "" ) <> "" ) ),
    CALCULATE ( [Sum Value1], COALESCE ( Earningssample[Category], "" ) = "" ) 
   )