4 Replies Latest reply on Jun 26, 2018 6:29 AM by Dustin Greelis

# Get the average of a field - weighted to the population

Hi folks,

I'm a market researcher, and so we use a lot of weighted numbers.  Basically, there is a column of 'weights' that represent the number of respondents that each row should count for.  So if respondent 1 has an answer of 5.5, and a weight of 10, then that should count for 10 responses of 5.5.  This is fine when getting the sum (drag the 'weight' field into the middle and presto!), but as you can imagine, dragging the 'weight' field into the middle and setting it to get the average gives a number that's way too high (in essence, multiplying the weight field of 10 by the answer of 5.5 to get 55).

Do you guys have any ideas on how to make this work?  I attached a file with random data, just showing that I'd like to make a trend line, but instead of the weight being the darkness of the line (as it is now), I want it to represent the number of times that the response in the same row (to Q1 in this example) should count.

Let me know if any of this is confusing.  Thank you folks so much for the help!!

-Dustin

• ###### 1. Re: Get the average of a field - weighted to the population

Hi

Not sure I understand your example but see the attached

I interpreted the problem as Q1 is already the weighted product of the answer and the number of respondents (weight)

and you wanted the avg response or the unweighted response

if that isn't what you wanted please clarify

thanks

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Get the average of a field - weighted to the population

Hi Jim,

Thank you for going through all the time responding to this with an example.  Unfortunately, this isn't what I was going for.  The responses you see in Q1 are unweighted.  So in the datafile, they only count as 1 response right now, and that's how Tableau reads it in to the charts/graphs.  I'm looking to have each response count for the number of responses that are in the weight column.

For example:

In the first row, the response to Q1 is 260.33, and the weight is 6.  I'm looking to essentially have Tableau count that 260.33 six times, and repeat the operation of counting each Q1 response by the number in the weight.  Then, apply an average to all those numbers.  So in the average calculation, the 260.33 would be counted 6 times.

It's kind of a tough thing to explain, so please let me know if you'd like me to elaborate.

Thanks again,

-Dustin

• ###### 3. Re: Get the average of a field - weighted to the population

see the attached

it returns this

in table form

jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 4. Re: Get the average of a field - weighted to the population

Thank you Jim!  The result in the AGG(avg per year) line is exactly what I was looking for!  I really appreciate your time and help.