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

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

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

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

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

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

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.