# How to Sum the aggregates of a child group

This should be easy, but I am stuck.

I have a table listing some figures about Qualifications - to achieve which a dataset that is essentially a row per Student is being grouped on Qualification with a Parent Grouping on "Measure" (which is just a bucket of qualifications).

One of the columns is trying to work out the number of students (well, more properly the number of students with a value in a particular field, weighted by another field) in each Measure/Qualification. In the screenshot below, it's the "Pred. Avg" column on the right hand side.

So for the Qualification Row Grouping, that column is calculated by:

=CountDistinct(Iif(IsNothing(Fields!AVG_PTS.Value) = False, Fields!Learner_ID.Value, Nothing), "Qual") * Lookup(Fields!Qual_Code.Value, Fields!Qual_Code.Value, Fields!size.Value, "DS_KS5Nationals_LKP")

This works fine - the values of 35 and 11.5 in that rightmost column are correct for those rows. What the top row should be doing is simply adding up the values in the other rows to give me the number of students in this Measure, in this case to give 46.5. To do that the expression I am using is:

=Sum(CountDistinct(Iif(IsNothing(Fields!AVG_PTS.Value) = False, Fields!Learner_ID.Value, Nothing), "Qual") * Lookup(Fields!Qual_Code.Value, Fields!Qual_Code.Value, Fields!size.Value, "DS_KS5Nationals_LKP"), "Measure")

However as you can see in the screenshot, this returns 2917 instead.

So my question is; Why doesn't that work, and given that it doesn't work how can I, within a parent group, aggregate the results of aggregates inside a child group?

EDIT:

OK so, I have determined that the following works correctly:

=Sum(CountDistinct(Iif(IsNothing(Fields!AVG_PTS.Value) = False, Fields!Learner_ID.Value, Nothing), "Qual"), "Measure")

The problem there is that the Qual row that returns 11.5 is weighted to 0.5. I.E. it actually returns 23, and the Lookup(Fields!Qual_Code.Value, Fields!Qual_Code.Value, Fields!size.Value, "DS_KS5Nationals_LKP") is for that row returning 0.5 and altering it to 11.5...so the question becomes; "how do I force that ...*Lookup(Fields!Qual_Code.Value, Fields!Qual_Code.Value, Fields!size.Value, "DS_KS5Nationals_LKP") into the "Qual" scope, like the CountDistinct() is already in?

The issue here is that you're trying to aggregate values using that Lookup function which only returns one value. There are a couple ways you could go about doing this. One option would be to use the LookupSet function to get the applicable weightings. An even better option is to combine the data in your dataset so that the weighting is available without using a lookup. That way the function can recalculate an any grouping level without you having to force a scope on it. Also, CountDistinct ignores "Nothing" so you can do without the extra IIf statement. Hope that helps.