2 Replies Latest reply on Jan 4, 2019 10:51 AM by Scott Henry

# Average of percentages, unweighted

Hello -

My goal is to understand how to average of unweighted percentages, instead of aggregating values before calculating. I need to see the average of percent difference without considering the weight of the percentages.

My scenario: what is the average percent tuition price drop among my selected colleges? What is the average enrollment change for those colleges?

My problem: the aggregation of the values prior to calculating the percent change weights percentages, where I want them unweighted, which seems counter intuitive to Tableau.

Example: College A costs \$10k and has 2,000 students in year 0; in year 1 they decrease cost to \$8k and have 3,000 students. So, cost -25% and enrollment +50%. College B  costs \$20k and has 10,000 students in year 0; in year 1 they decrease cost to \$18k and have 9,000 students, so cost -10% and enrollment -10%. I want to see that the overall average cost change among colleges was 17.5%, and overall enrollment change was +20%.

Of course, Tableau is going to tell me that average decrease was 13.3%, and the enrollment didn't change at all 0.0%. Because those values are aggregated, then averaged, that way they are weighted comparatively. Which is all true. But not the question I'm trying to answer!

I want to aggregate all the percentages, THEN take the average of those percentages, NOT the overall average of the aggregates! It's driving me crazy. Please help?

In the workbook, I would expect the 'Grand Total' for year 0 to be 2.83%, NOT -1.1%. Thanks for the ideas!

• ###### 1. Re: Average of percentages, unweighted

Hi, Scott

Below is the screenshot and explain of steps

Hope this helps

ZZ

1 of 1 people found this helpful
• ###### 2. Re: Average of percentages, unweighted

Thank you Zhouyi Zhang! You're a star.

If I'm understanding, the problem is that we need the last year value added in our data set so that we can perform the fixed calculations without them being a table calculation. That way, we can fix the last year value on college by year since price drop, fix the current year value on college by year since price drop, then run the difference, and divide by the last year value.

That makes good sense, and better yet, it really really works. Adding the join with the prior year's data was crucial to solving this problem. Huge thanks!