1 2 Previous Next 26 Replies Latest reply on May 25, 2018 9:01 AM by Ásmundur Pálsson

# Multiple Response Calculation

Hi there,

I'm sure this is an easy one, but I can't quite get there.  Tableau, v9.2, survey data has been pivoted.

Essentially, I'm trying to get Tableau to treat a certain question as a multiple response question.  However, it's treating it as a single response, so my actual counts are correct but the percentage is wrong since Tableau isn't calculating based on the number of people who answered the question.

A: 200

B: 150

C: 100

Above is an example - those are the number of people answering each answer option.  I'd like Tableau to calculate % based upon the number of people answering that particular question, not the total number of people answering (e.g. Tableau adds up all responses and uses that as the base size (450 in this example).

The calculation being used (the incorrect one), is:

SUM([Weight2]) / TOTAL(SUM([Weight2]))

Weight2 is a weighted field.

I do have a unique ID field, I suspect I need to integrate that is some way?

The multiple response question is not set up as a Yes/No type of question.  Each response has 8 choices.  I wonder if that's the problem?

Oh, I have added the multiple response question by going into my pivot field dimension, and simply selecting my 10 "questions" - each question having the 8 response mentioned above.

Any ideas?

• ###### 1. Re: Multiple Response Calculation

Edit: It definitely has something to do with the % being calculated as a Table Down calc.

• ###### 2. Re: Multiple Response Calculation

Hi Kevin

Do you have a workbook ****.twbx that you could post -

I think you will need an LOD expression that calculates your percentage over each question - not clear what your Dimensions and Measures are from the above -

but it could take the form of Fixed  {Question] : SUM([Weight2]) / TOTAL(SUM([Weight2]))}

but that is only a guess - not sure what weight 2 is

Jim

• ###### 3. Re: Multiple Response Calculation

Hey Jim!

Thanks so much for the response.  Your formula looks like the kind of thing I'm after, sir.  I'll try that.

Posting this one as a .twbx might be a bit of a pain - super confidential survey data for a big client.  I'll see if there is a way I can anonymize it.

Thanks again for your suggestion.  I'll post the result!

• ###### 4. Re: Multiple Response Calculation

Ok give it a try - I do have a question about your data though -

I have done work on survey data and very often the responses to a given question are in columns - that is 8 possible responses to a questions are in 8 different columns.  If your data is in that format you will probably want to pivot the data for each question so that you end up with data in rows - that way the question will show up as a Dimension and the responses will be a Measure

Let me know

Jim

• ###### 5. Re: Multiple Response Calculation

Hey Jim,

Great question/comment. Yessir, the data is pivoted successfully and integrated into the dataset.

I've tested this 2 ways now and keep getting the same result.  2 versions of the same multiple response question.  I've tested using the following formats:  Example (fake) question - what's your fave color:

Version A:

Response 1: Red (1) White (2) Green (3) Black (4)

Response 2: Red (1) White (2) Green (3) Black (4)

Response 3: Red (1) White (2) Green (3) Black (4)

Response 4: Red (1) White (2) Green (3) Black (4)

Version B:

Red (1)

White (2)

Green (3)

Black (4)

Both formats yield the same response, i.e. the counts (frequency) is correct, but the percentages are whacky.  I think the problem is that this multiple response question was only asked for people who answered another question a certain way.  I'd like my percentages to be calculated based upon the total number of people answering that question.  I can do this easily in SPSS - but this is killing me!

• ###### 6. Re: Multiple Response Calculation

OK I have a solution for you and will walk you through it - I am on t10.1 see the attached excel sheet that I used as input

several points:

1. First you want you data file to be a single record for each respondent (disagregated
2. Yes the fact that you have dependency between questions complicates the process
3. you will need to calculate the number of responses that you want to use as the base for each of the dependent questions using an LOD expression
4. I had 3 questions Age, Color, Fruit - Only respondents age 30 could respond to the color questions I further counted ages 30-40 as the basis for % color calculation - all could respond to Fruit including a 50 year old
5. This is used as the basis for the color % >> { exclude [Q1 Color]:     sum( (IF [Age]>=30 and [Age]<=40 then   ([Number of Records]) End))}

1. The % calculation is   >> count([Q1 Color])/sum([Count of age 30-40])
6. Likewise the calculation for the basis for % of Fruit >>   { exclude [Q1 Color], [Q2 Fruit]:    sum([Number of Records])}

1. The % calc is >> count([Q2 Fruit])/sum([count of all respondents])
7. When you do that you should end up with something that looks like this
8. You count the color or fruit responses with a zn formula  like this for color    >>zn(count([Q1 Color])) let me know if this helps

Jim

• ###### 7. Re: Multiple Response Calculation

Hey Jim - this is great.  I'm looking into this - I'll update the thread with my progress, sir!

Thank you again.

• ###### 8. Re: Multiple Response Calculation

Thanks

I am going to try to convert the T10.1 file I have to a TWB 9.2 file that you can see - I am not sure about the conversion tool but I will give it a try

Jim

• ###### 9. Re: Multiple Response Calculation

See if you can open this - at least you can see what I did

Jim

• ###### 10. Re: Multiple Response Calculation

Jim - most excellent.  The file works perfectly and is extremely helpful.

You're totally right - I need a calculation which calculates my base size for that particular question, and then incorporate that base size to correctly calculate the percentages.

This is so close! I think the trick is, all my data has been pivoted.  Tell you what, I'm going to see if I can re-create something similar to my actual file, and then upload.

Jim, you've been really gracious with your time, sir.

• ###### 11. Re: Multiple Response Calculation

If you have any more questions just let me know - my profile is posted

BTW - if you get a chance please mark the original response as correct response

Thanks

Jim

• ###### 12. Re: Multiple Response Calculation

Jim,

Thanks again, this totally helped.  I have a part 2...I'll post it here but maybe I'll move it since it might be a different thing.

The weighted data seems to be problematic on one type of question.  I have a Measure, WeightedData2.  I am using this for all my calculations and it's working out great.

The only one it's not working out with is this particular question you've been helping me on.  The problem is this: Tableau is using my unweighted data for the base size instead of the weighted data.

So, while the Number of Records measure successfully shows the total number of people answering the question, it's not the weighted number of records.

For the life of me, I can't figure out how to incorporate the Number of Records and Weight measures to work together.  Any ideas?

• ###### 13. Re: Multiple Response Calculation

Thanks

I think the weighting issue comes from the formulas I sent you they are based on record count

Count of age 30 -40 >>{ exclude [Q1 Color]:     sum( (IF[Age]>=30 and [Age]<=40

then   ([Number of Records])

End))}

% Color >> count([Q1 Color])/sum([Count of age 30-40])

Which in effect does not weight the response - If you change that number of records to some weighted measure it should fix the issue

Let me know

Jim

• ###### 14. Re: Multiple Response Calculation

EDIT: Number of records perhaps isn't accurate.  Here's how I have things set up:

2 calculated fields:

Response:

sum([Weight2])/([Totaler])

Totaler

TOTAL(COUNTD([ID])) <--this is what I'm using to calculate the overall number of people answering the particular question (i.e. the questions base size)

The problem is my Totaler field is unweighted - that's what I need to be weighted!  For the life of me I can't figure it out.

1 2 Previous Next