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

Get the average of a field - weighted to the population

Dustin Greelis Jun 25, 2018 1:04 PM

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

Weighted_Average.twbx 23.7 KB

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

Jim Dehner Jun 25, 2018 1:33 PM (in response to Dustin Greelis)
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.

Weighted_Average_v10.2.twbx 21.0 KB
Like Show 0 Likes(0)

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

Dustin Greelis Jun 25, 2018 1:46 PM (in response to Jim Dehner)

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
Like Show 0 Likes(0)

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

Jim Dehner Jun 25, 2018 2:35 PM (in response to Dustin Greelis)
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.

Weighted_Average_v10.2_v10.2.twbx 22.6 KB

1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
4. Re: Get the average of a field - weighted to the population

Dustin Greelis Jun 26, 2018 6:29 AM (in response to Jim Dehner)

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.
Like Show 0 Likes(0)

Actions