Jan 13 2020 12:03 PM
Hi all!
I'm so excited to join this community. I was hoping to find some help about an issue I haven't been able to figure out on my own. I have attached a screenshot of a simplified version of the issue for reference.
I need the weighted counts of each category (the Count*AVERAGE(WEIGHT) Column) to appear in the pivot table by themselves. So in this case, 7.45 for Apple and 2.6 for Banana. I need to be able to do this for as many variables as possible, and I need it to work with filters. For instance, if I chose to filter to show only people who selected Blue, it would show .85 Banana and (1.15+1.25+0.9+1.05) Apple. This calculation can be done by multiplying the count by the average of the weights, I hope that the filtering function behind pivot tables can also keep the weights assigned to each column to average them out.
I know that I will need to do a calculated field multiplying the pivot table count column by the average of the weight column. I would like to be able to do this automatically for all columns, so any new column I add (say "favorite animal") would show up as weighted if I moved its data into the pivot table.
Thank you so much!
Jan 13 2020 01:10 PM
Since you managed to count the number of Fruit, I'm confident that you can also drag the Weight field into the value area of the pivot table and change its attribute to "Average" rather than "Sum".
Jan 14 2020 05:45 AM
Yes, but how do I multiply the average by the count within the pivot table and only show the end value?
Jan 14 2020 06:12 AM - edited Jan 14 2020 06:14 AM
I don't follow. Count X Average Weight would be the same as SUM of Weight. You can test it by adding another value field, like:
By the way, I deleted my example that I used in my first response. Therefore you see Name and Value in stead of Fruit and Weight.
Jan 14 2020 11:42 AM
Yes, is there anyway to (1) Only show the sum of weight in the pivot table, and (2) write the formula so that it multiplies the weight by the first column, regardless of what the first column is (e.g. I can switch out the fruit column for the color column, or add any additional column without having to create a new calculated field to for every new column)? @Riny_van_Eekelen
Jan 14 2020 12:22 PM
Not sure I follow. Sorry! Please clarify and/or upload a file that demonstrates what you mean.
May 08 2020 02:20 PM
Hi Matt
I am trying to do the same thing you are, did you ever find a way of accomplishing using a weighted average with a count function?
Thanks!
Toni
Jan 17 2023 02:10 AM
@Matt_Vaughn @Toni_Roschtscha
did either of you ever find a solution? I'm trying to figure out the same thing