Weighted averages (need to avg group averages)

[flyerone]

I need a way to average a previous group of averages. Example I have a user with 3 tickets that have different data line records for each ticket that are averaged, so I need to average those 3 ticket averages to come up with a average for the user per ticket. Because you cannot use aggregate function in aggregate function I do not know how to do this.

ticket 1 avg 345
ticket 2 avg 567
ticket 3 avg 239
user avg 383.67

Because the tickets have different record used to come up with their averages I cannot add all records together and divide by the number of tickets to come up with the average this average is incorrect.

 

[jymm]

check out the RunningValue function - I do something similar in a table - although averages of averages really do not work for me mathmatically - may just be me... maybe it does make sense in your case. Let us know if this does not work.

in one app I also resorted to creating another dataset in SQL to get the sums to work right in SSRS... so you might think about that as it was MUCH easier to join two select statments than to get the answer the execs wanted through SSRS's functions like aggregate, sum...

this help or hinder?

 

[flyerone]

Thanks for the suggestions, I tryed RunningValue but it only gave me the sum for all groups not each group. If I could use aggregate function in aggregate function I could do this. In Crystal I use running totals and reset the total after the main grouping, but I can not see any way of doing that in SSRS. I have thought about the SQL to create the sum fields, I might need to try that nexted.

 

[xlbo]

General tip - do as much of your calcs in the SQL as possible. SQL is optimised for this kind of stuff. SSRS is not. SSRS is optimised for formatting / presentation, not that hot for complex calcs

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question