1 Reply Latest reply on Sep 26, 2017 9:16 AM by Joe Oppelt

    Action filter should not affect the % Table calculation

    himabindu tipirneni

      Hi,

       

      I have a table which filters out the top 10 values in a table. This is in turn affecting the % of Total Weight column since it is only taking in to account these 10 values.  I want to be able to still filter out the table (action filter to show top 10 - Button functionality) and have the % Total Weight column to consider all the values and not just the top 10.

       

      Any help is deeply appreciated.

       

      Regards,

      Bindu

        • 1. Re: Action filter should not affect the % Table calculation
          Joe Oppelt

          An action filter becomes a quick filter in the destination sheet.  That's how it works.  And a quick filter eliminates rows.

           

          The way to pare down displayed rows without eliminating rows from the underlying table is to use a table calc as a filter.  (You cannot pass a table calc as an action filter dimension though.)

           

          Have your action filter go to the destination sheet without passing the 10 key values for your top-10.  In your destination sheet, use a table calc to display only those top 10.  Something like this:

           

          IF RANK(sum(values)) <= 10 then 1 else 0 END

           

          Put that on your filter shelf and select for value = 1.  You'll display your top-10, but the whole underlying table will still be intact, and your % of total will still behave properly.