8 Replies Latest reply on Apr 18, 2013 9:36 AM by Brad Llewellyn

# Percentages of grouped columns

I'm having an issue with calculating a percentage of some of the grouped columns that I have.

When ever I use the Analysis > Percentage of > Column function it comes up with an answer greater than 100%

Any ideas why, or how to fix this?

• ###### 2. Re: Percentages of grouped columns

Ben,

This is a mathematical quandary with using Count Distinct.  The issue is that Tableau will count the number of distinct Respondents for each answer, then divide that by the distinct count for the entire data set.  Therefore, if Respondent 1 was to answer the question twice, he would be counted twice in the answers, but only once in the total.  There are a couple of ways you can alleviate this:

1) Clean your data so that each Respondent has only one answer for each question.  Then, you can use COUNT() and you will not have this issue.  This is the mathematically correct way.

2) Instead of using the Quick Calculation, create the following calculated field

COUNTD( [RespondentID] ) / WINDOW_SUM( COUNTD( [RespondentID] ) )

This way may lead to incorrect conclusions due to the double counting that was mentioned earlier.

Hope this helps,

Associate Consultant

Mariner, LLC

http://breaking-bi.blogspot.com

1 of 1 people found this helpful
• ###### 3. Re: Percentages of grouped columns

Yeah, that might be the issue as these are multiple choice questions, is there a better way of analysing them in that regard?

That formula doesn't seem to work unfortunately, just returns 1.0 every time?

Thanks,

Ben

• ###### 4. Re: Percentages of grouped columns

Ben,

If they are truly multiple-choice, meaning that a single respondent can answer more than once, then you need to use COUNTD() just like you are using.  The numbers will not add up to 100%.  This is mathematically correct.  Simple case, I answer A and B.  Chart looks like

A     100%

B     100%

C      0%

Total ---

Are you okay with this?

Associate Consultant

Mariner, LLC

http://breaking-bi.blogspot.com

1 of 1 people found this helpful
• ###### 5. Re: Percentages of grouped columns

Yeah, that makes sense.

Is there an easy way to count forwards: I.E. If someone answers A&B vs. C

... I can just group the data right?

• ###### 6. Re: Percentages of grouped columns

Ben,

That's an interesting question, which goes down a very different path.  If you want to know which respondents answered ONLY A or ONLY A and B, then a rework of your data source would be advised.  For example,

1                         1                   Only A

1                         2                   A and B

2                         1                   Only C

In this setup, each respondent would only answer each question once, where A and B is considered a completely different answer than ONLY A.  In this setup, you could also use simple COUNT() and TOTAL() to get accurate percentages, which would add up to 100%.  Is this the path you want to take?

I subscribe to a simple philosophy in Tableau.  "A minute in the back-end is worth an hour in the front-end"

Thanks,

Associate Consultant

Mariner, LLC

http://breaking-bi.blogspot.com

• ###### 7. Re: Percentages of grouped columns

Aha, that makes a lot of sense!

To save a rework of the data, I'll probably just go with the grouping method for now, as I can get the answers I'm looking for there, but for future sets, this is definitely the method. - Although, how would you then split that out again to A, B & C variables individually?

• ###### 8. Re: Percentages of grouped columns

Ben,

Unfortunately, these two methods require pretty intense manipulation to move between one and the other.  To swap back to A, B, C format, you could create three calculated fields.

Calculated Field "Answer A"

WHEN "ONLY A" THEN 1

WHEN "A and B" THEN 1

WHEN "A and C" THEN 1

ELSE 0

END

Rinse and Repeat for Calculated fields "Answer B" and "Answer C"

A similar method should work going from A, B, C format to ONLY A, A and B, etc. format.

Hope this helps,