How to count occurrence of value and percentage of a subset in tableau public?

0 votes

I have a set of data in the following format:

Resp | Q1 | Q2
P1   | 4  | 5
P2   | 1  | 2
P3   | 4  | 3
P4   | 6  | 4

I'd like to show the count and % of people who gave an answer greater than 3. So in this case, the output would be:

Question | Count  | Percent
Q1       |  3     | 75%
Q2       |  2     | 50%

Any suggestions?

Jun 5, 2018 in Tableau by ghost
• 1,790 points
1,662 views

1 answer to this question.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
0 votes

Although it sounds like a fairly easy thing, it is a bit more complicated. Firstly your data is not row based so you will have to pivot it.

  1. Load your data into Tableau
  2. In the DataSource Screen choose column Q1 and Q1, right click on them and chosse "Pivot"
  3. Name the column with the answers "Answers" (just for clarity. You should get a table that looks like this:

enter image description here

Now you need to create a calculated field (I called it Overthreshold to check for your condition:

if [Answer] > 3 then
[Answer]
End

At this point you could substitute the 3 with a parameter in case you want to easily change that condition. You can already drop the pills as follows to get the count: enter image description here

Now if you want the percentage it gets a bit more complicated, since you have to determine the count of the questions and the count of the answers > 3 which is information that is stored in two different columns.

  1. Create another Calculated field with this calculation COUNT([Overthreshold]) / AVG({fixed [Question]:count([Answer])})
  2. drop the created pill onto the "text" field or into the columns drawer and see the percentage values
  3. right click on the field and choose Default Propertiess / Number Format to have it as percentage rather than a float

enter image description here

To explain what the formular does: It takes the count of the answers that are over the threshold and devides it by the count of answers for each question. This is done by the fixed part of the formular which counts the rows that have the same value in the Question column. The AVG is only there because Tableau needs an aggregeation there. Since the value will be the same for every record of the question, you could also use MIN or MAX.

It feels like there should be an eassier solution but right now I cannot think of one.

answered Jun 5, 2018 by Atul
• 10,220 points

Related Questions In Tableau

0 votes
1 answer

How to dynamically determine and categorize duplicate value in Tableau?

Apparently you define a duplicate records as ...READ MORE

answered Apr 12, 2018 in Tableau by xyz
• 1,550 points
1,229 views
0 votes
1 answer

How to calculate the percent of records within a group in tableau?

 You can click the measure SUM(Number of Records) and ...READ MORE

answered May 8, 2018 in Tableau by Atul
• 5,490 points
73 views
0 votes
1 answer

How to get multiple Sets of Measure Names with different Filters on a single sheet in Tableau?

The easiest solution to your query is: 1) ...READ MORE

answered Aug 23, 2018 in Tableau by Naruto
• 710 points
755 views
0 votes
1 answer

In Tableau, How to sum the last 7 days of the value?

It can be achieved easily by creating ...READ MORE

answered Mar 12 in Tableau by CHERUKURI
• 14,900 points
117 views
0 votes
1 answer

How can I count the aggregate function in Tableau

Create two calculated fields one for 100's ...READ MORE

answered Apr 5, 2018 in Tableau by Atul
• 10,220 points
1,055 views
0 votes
1 answer

How to use LOD to count distinct number of records based on some condition?

From the desired output it seems that ...READ MORE

answered Apr 9, 2018 in Tableau by xyz
• 1,550 points
2,360 views
0 votes
1 answer

How to aggregate and count the occurrence of TRUE and FALSE?

Hi, you can do any of below ...READ MORE

answered Mar 12 in Tableau by CHERUKURI
• 14,900 points
57 views
+1 vote
1 answer
0 votes
1 answer

How to color code cells of a column based on the text value in Tableau

You can use the following steps to ...READ MORE

answered Mar 27, 2018 in Tableau by Atul
• 10,220 points
1,090 views
0 votes
1 answer

how to get the count of non zero values in a row

In first part you should solve the ...READ MORE

answered Apr 4, 2018 in Tableau by Atul
• 10,220 points
92 views

© 2018 Brain4ce Education Solutions Pvt. Ltd. All rights Reserved.
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.