April 4, 2011 at 7:51 am
I've been using SSRS 2008 for about 7 months but am really struggling with how to calculate an average of an average in a matrix. I've read somewhere online about adding a function in the "subtotal" row which returns the average of the detail rows whilst at the same time incrementing a total and count. Another function placed in the "grandtotal" row =total/count. This works fine for a table, but gets more complicated in a matrix as I want the calculations to work for each column too. I've tried another function which tells me the column number and I am trying to pass that as a parameter as well as the average for the subgroup. I get a warning "Object reference not set to an instance of an object" when I try to run it and #Errors in the subgroup average box.
To give some context of what I am trying to do, I am looking at school report grades over several sessions and want to average the grades per student per subject per session then aggregate them into an average of the grades per student per session. I could have different numbers of grades for the subjects studied and don't want to give extra weighting to those subjects with more grades.
Thanks for your help.
Sue
April 4, 2011 at 8:05 am
I have no other idea than the sum / count.
Have you considered calculating that 1st avg in the main select? That would take care of the ssrs challenge.
I know it's somewhat moronic to do the same base query twice on sql, performance wise as well. But hopefully it won't hurt the server too much.
April 4, 2011 at 9:24 am
On futher investigation it seems that there are values that could be null in the column that I want to average. I set those values to null so that I could ignore them in the averages, but sometimes all the values to be averaged are null. I did try an average if not 0, but that didn't work either. Would a test of the count of the values being IsNothing or null or 0 or something and only running the code where a non null average is found solve my problem do you think?
Thanks
Sue
April 4, 2011 at 9:32 am
At this point your ideas will be better than mine. I never, ever attempted what you're trying to do. You'll have to test and tweak untill you get it to work.
April 4, 2011 at 10:15 pm
It's my understanding that calculating an average-of-an-average is one of those Mathematical "no-nos". You might want to Google it and find out all the reasons why its mostly an invalid calculation.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 5, 2011 at 1:56 am
That is an interesting point Jeff. Let me try and explain better why I am attempting it.
A student studies some subjects and is awarded grades by each teacher. There are 3 art teachers, who all give A* (equivalent score 8), 1 Business Studies teacher who gives B (score 6) and 2 Maths teachers who give A, B (7, 6). Now if I average the normal way I have avg(8,8,8,6,7,6,) = 7.17. This gives an artificially high score for Art. If I want to give equal weighting to each subject I need an average for Art = 8, Business Studies = 6 and Maths = 6.5. Averaging these 3 subjects gives 6.83. This is a better reflection of how the student is doing overall and is not skewed by the number of teachers giving grades at any one time per subject.
April 5, 2011 at 3:43 am
Mathematically speaking Jeff is correct - you can't take an average of averages and expect it to represent the true overall average (although it might!) - it all depends on what you are averaging. Also, your comment that your method does not skew the overall grade by placing too much weight on the Art department could also be flawed in that it places more weight on the grade of a single Business Studies tutor than of three Art or 2 Maths tutors. Also, are the grades given by each tutor equivalent - do they all represent the outcome of a similar number of tests? You can only truly take a mathematical average of comparable values - don't try comparing apples with oranges.
Consider this scenario: the student takes three tests for each of the three Art tutors, three test each for the Maths tutors, and a single test for the Business Studies tutor. If the student has a bad day in an Art or Maths test, it only effects 1 out of many tests for that subject and will have little effect on his overall grade as you are calculating it, but if he flunks the single Business Studies test, it would grossly skew your overall mark, an effect not noticed if you took a true mathematical average of all grades received.
The moral? Be careful that what you are calculating is what your users really expect!
Tony
April 5, 2011 at 3:57 am
Good point Tony. Maybe what I should have made clear is that these grades represent each teacher's opinion of the work done in a half term against criteria that are used for all subjects. It is never based on a single day or piece of work. (When I look at exam marks, we expect standardised marks to be given so I can compare subjects.) What I am aiming to do is to put this all in matrix so that we can get a picture of whether a student is improving or not over period of time, both at the subject level and overall. I'm providing drill downs so that initially only the summary data is shown but that teachers can expand down to the level of the individual teacher's grade for each session if required.
Thanks for your input.
April 5, 2011 at 4:18 am
Fair comment, and I appreciate what you are saying.
However, my point about your method also skewing the result still stands. The opinion of the single Business Studies tutor carries more weight than the that of the tutors in the other departments, so if he or her marked a student down for some reason (we all know it happens, although I am sure that all your tutors are scrupolously fair 😉 ), then that grade will have an undue effect on the final result.
In the end, what matters is that you and your colleagues are aware of the impications of the method of calculation and are content that it is satisfactory.
Tony
April 5, 2011 at 4:28 am
Ah yes, I get your point. It looks like no solution is going to entirely iron out these skews.
However, should it be deemed that what I am aiming to do is fairer than the alternative, I'm still stuck on how to implement this, especially as sometimes there are N grades (which equate to 0) which were allowed at the beginning of the year where the teacher hadn't seen the student for long enough to comment. I want to eliminate all those from my calculations. I might try doing that at the sql stage as trying to do conditional averages or even counts is not working for me yet.
Thanks for all your advice
Sue
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply