5 Replies Latest reply on Aug 6, 2013 10:44 PM by Dimitri.B

    Showing only some values as result of table calculation

    CJ Ragnarsson

      I have made a Table calculation to show percentage of total. For example, on the below data, it would show:

       

      Data:

      Cat 3

      Dog 3

      Ant 4

       

      Table Calculation:

      Cat 30%

      Dog 30%

      Ant 40%

       

      Now, I want the user to be able to select which animals to show this result for. For example, he might want to see the result of the table calculation for Cats only, or for Dogs and Cats (either individually or as a total).

       

      Excluding values or creating a set means the user is not able to adjust it (sets that could be changed with checkboxes would have worked, but as far as I understand, they don't exist). Letting the user filtering the data by animal would normally change the result of the table calculation.

       

      I could add an extra column to the data (the total), but this is not very elegant, and will naturally create some limitations. Is there a better way?

        • 1. Re: Showing only some values as result of table calculation
          Dimitri.B

          You can use table calculation to trick Tableau into keeping all the data in the background while displaying only selected items.

           

          The formula is:

           

          LOOKUP(ATTR([Product]),0)

           

          See attached workbook for an example, where you can choose what products to display or hide without changing their % of total values.

          I can't tell if it will work in your case, but you might also consider dashboard actions to get similar functionality.

           

           

          BTW - nice cat.

          • 2. Re: Showing only some values as result of table calculation
            CJ Ragnarsson

            Very cool - does that count as a feature or a hack?

             

            The correlation between my cat (a default image?) and the choice to mention cats in the question was 0.

            • 3. Re: Showing only some values as result of table calculation
              Dimitri.B

              This is one of those grey areas - depends who you ask. I'd say it is a 'gentle and benign' hack, some will consider it a feature. I believe the reason it works is a side-effect of table calculations, not developer intentions.

               

              What is the cat's breed? He (she?) has this "deep in thought" look - working with Tableau?

               

              • 4. Re: Showing only some values as result of table calculation
                CJ Ragnarsson

                A bonus question:

                Your answer basically answered my question, but I am not getting it to work 100%.

                 

                Say I introduce 2 table calculations by sales in the same sheet (well, I guess it actually doesn't really matter what they are), one which splits sales by Product, and one by Type (Decaf or Regular).

                 

                In this case, the Select Product Sheet shows a *, which I'm assuming corresponds to all data which is not split by Product. As long as I keep the *, the other data will stay the same. Is this the right understanding? Is there some deeper and better level to understand this?

                 

                I get this to work in your example spreadsheet, but I'm getting some erratic behavior in another workbook (which I can't share, unfortunately) where some of the other data which is not broken out by "Product" still disappear when I filter out some of the "Product" categories in this manner. The erratic behavior seems to happen for categories with very little data to start with.

                • 5. Re: Showing only some values as result of table calculation
                  Dimitri.B

                  Table calculations can be temperamental and confusing. Their behaviour depends on what else is on the sheet.

                  Can you re-create your scenario in the attached workbook?

                   

                  The * must be the result of ATTR() function, which means that there is a bunch of different values behind it. That's how it works - if there is only one repeating value in rows behind - ATTR() will show that value, if not - it will show *.