I have a school data set which contains several data sources - teachers, classes, students, advisers, etc. Multiple teachers can become an adviser to a class.
I'm trying to create a calculated field under the classes data source which will tell me how many advisers there are for a particular class, but having trouble referencing different data sources in the calculation.
- IsAdviser (either 1 or 0)
- new calculated field (NumberOfAdvisers)
I'm thinking the calculated field should be created in the ClassesData data source, and is calculated thusly -
NumberofAdvisers = sum of AdvisersData.IsAdviser where (ClassID = AdvisersData.ClassID)
Is there a for loop or aggregating function I can use to get the above field?
Please provide a small sample data set or any working work book to solution the problem posted.