4 Replies Latest reply on Jan 14, 2020 1:57 PM by nick fasulo

    Creating a '% of total' table based on count of multiple text values

    nick fasulo

      Trying to create a table in Tableau that has '% of total" of each response in PERCEPTION OF BRAND column; organized by 'no' and 'yes' for OWNS BRAND PRODUCT(s) based on this table:

       

       

      In Excel, I would just create a handful of COUNTIF functions and divide by the total. Output would look something like this:

       

      At a fundamental level, the biggest thing I am trying to learn is getting the count/recurrence of a value in a column when the values are text based. From there, I think I can merge a few calculations together to get what I need.

       

      Thank you!

        • 1. Re: Creating a '% of total' table based on count of multiple text values
          Johanne Lemaire

          Hi Nick,

          You can put "Owns Brand Product" on the Rows Shelf, and "Perception of Brand" on the Columns Shelf.

           

          Step 1

          That will give you this view:

          (If you get something different, use the "Show Me" button to switch to Table).

           

          Step 2

          Next step is to drag the "Perception Brand" pill from the left (Data) column to the Marks Area, on the Text Square.

          The result looks wonky, I give you that:

          Step 3

          Next step is to change a few settings:

          Hover over the "Perception of Brand" pill on the Marks card, to see the down arrow appear, then select Measure and Count

           

          Which gives you a count of each response, like so:

          Step 4

          You are almost there. The last step is to use a table calculation to find the % of total per row:

          On the same pill that we just changed to "Count", we are now going to select Quick Table Calculation/Percent of Total

           

          And the table is ready:

           

          Please let me know if this works for you, or if you have any other questions!

          • 2. Re: Creating a '% of total' table based on count of multiple text values
            nick fasulo

            Yes that helps thanks you.

             

            Additionally, if I just wanted to include "Great" or the total % of "Great" + "Good", is there a calculation I can create to do that?

             

            How can I get a count and % of total of a specific value?

            • 3. Re: Creating a '% of total' table based on count of multiple text values
              Johanne Lemaire

              Hi Nick,

               

              In that case you would create calculated field (double-click in the Analysis sidebar), to create your new values.

               

              You can change the If statement as needed.

               

              Now drag your new Dimension/blue pill on the Columns Shelf, where you have "Perception of Brand" and remove the original Perception of Brand pill.

               

              If you wanted to only show the percentage of Great + Good, you can hide the OK and Poor column.

               

              Which gives you this:

              I am attaching a packaged Tableau Workbook, where you can look at everything I did.

               

              If this is helpful, feel free to mark your question as closed, if you need more, feel free to ask more!

               

              Cheers, Johanne

              • 4. Re: Creating a '% of total' table based on count of multiple text values
                nick fasulo

                This is incredibly helpful! I often use Tableau to analyze survey data, which has text-based values (ex: select one from a drop down).

                 

                This simple calculation will be invaluable going forward for getting a count of a specific number in a table format.