Forum Discussion

abloor's avatar
abloor
Icon for Helper IV rankHelper IV
6 years ago
Solved

Stop % from summing in subtotal row

Hi, Can anyone please suggest how to stop a % measure from adding up in a subtotal row?  I want the subtotal row there, but want it to be blank for the List % and Var % columns as pictured, s they ar...
Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    abloor try this measure

     

    TotalShare% = 
IF ( 
ISFILTERED( Table1[Colour] ), 
CALCULATE(SUMX('Table1','Table1'[Share %])) 
)

     

abloor's avatar
abloor
Icon for Helper IV rankHelper IV
6 years ago

Sorry I just created generic sample data for the files uploaded and thought the column would be obvious - oops clearly not!

 

It's the TotalShare% column in my sample that is summing in the subtotal row, and I don't want it to. (In my real file the column that I'm having the issue with is a stand alone column like this one and doesn't link to or feed off any other columns, just like in my sample file.)

 

Thanks!

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

abloor try this measure

 

TotalShare% = 
IF ( 
ISFILTERED( Table1[Colour] ), 
CALCULATE(SUMX('Table1','Table1'[Share %])) 
)

 

  • abloor's avatar
    abloor
    Icon for Helper IV rankHelper IV
    6 years ago

    Thanks parry2k  I have successfully got it working in my report.

     

    Appreciate your time!!  🙂

    • abloor's avatar
      abloor
      Icon for Helper IV rankHelper IV
      6 years ago

      Hi parry2k ,

       

      I'm using your solution for a new matrix now, and I have to filter one of the fields so it's 'not blank'.  This makes the row subtotals show up again, despite your measure having taken it out as expected before I filtered out the blanks.  

       

      Do you think I'd have to factor in this new filtered field into the measure to?

       

      Thanks

      • abloor's avatar
        abloor
        Icon for Helper IV rankHelper IV
        6 years ago

        For anyone finding this post in future, I worked it out to included another filter of BLANK.

         

        TotalShare% =
        IF (
        ISFILTERED( Table1[Colour] ) && [Total Revenue] <> BLANK(),
        CALCULATE(SUMX('Table1','Table1'[Share %]))
        )