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 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] ) )
    
)

 

 

  • ak77's avatar
    ak77
    Post Patron
    2 years ago

    parry2k , Thanks again for providing measure. it did worked for the sample PBIX i provided yesterday but when i tried the same measure  with the original dataset the main total is displayed blank. Can you please help. i have attached the pbix for reference . Thanks again

     

    Sum Value1 = SUM ( Earningssample[Value1])
     
    Sum Value1 for Display =
     SWITCH (
        TRUE (),
        HASONEVALUE ( Earningssample[Category_Classification] ), CALCULATE ( [Sum Value1], KEEPFILTERS ( NOT ISBLANK ( Earningssample[Category] ) ) ),
        CALCULATE ( [Sum Value1], ISBLANK ( Earningssample[Category_Classification] )) )

     

    https://drive.google.com/file/d/1uRqkCd5TEhMVqi4eTdJDAEDKCfyVnDbv/view?usp=sharing