If you have the academicyear in the view somewhere - as a discrete (blue pill) - , then SUM([admssn])/COUNT([admssn]) will be generated for each academicyear (and the combination of other discrete pills in the view), and that way you won't have to adjust your calculations as there are new years.
For the calculation as is, you'd need to wrap academicyear in one of the aggregation functions such as ATTR(), MIN(), MAX(), etc. I prefer ATTR() because it returns a value if there is one and only one value in the data range and * (a special version of Null) otherwise. You'd still need academicyear in the view to make sure that ATTR() is returning only value, and in that case you're just as well off using my suggestion above.
Actually, if you don't mind, Jonathan, I have another quick question.
The calculation gives me a yearly average as long as there is only the discrete (blue pill) Academic Year variable in the view. However, if I include another blue pill, discrete variable, such as Institution Name, in the same sheet and try to use my newly created calculated variable (average yearly admissions), it actually breaks down across institutions and becomes the same as the sum of admissions per institution per year. My next calculation is to calculate (institution-specific average yearly admissions)/ (average yearly admissions). That does not seem possible in this case, is that right? Would I need to follow the second guidelines to do that?
Thank you again!
Hi Leonor, what you are looking for there is calculations at different levels of detail, where there's the average admissions per year+institution and the average admissions per year. In Tableau, this generally calls for table calculations. I set this up in the attached, with:
Year = YEAR(Order Date)
Institution = Category
Yearly Admissions = Admission Standin - using SUM(Sales)/COUNT(Sales)
Based on the Yearly admissions, the formula [Admission Standin]/TOTAL([Admission Standin]) with Compute Using set to Category (so it partitions on the Order Date) gets the desired results.
Thank you again; your response was very helpful !