calculating weighted averages on an SRS report
Posted on 2010-11-11
I have been around in circles with this and cannot figure this out. I am trying to get the weighted average on a sql report based on a count and an amount.
So first I get the average price of something we sell by creating an expression: Price/Count
(it will not let me do a calc field because I am using the count aggregate function)
We only sell 4 major items, which I am basing my report on. So I know that the next step is to take each of my item's average price, multiply it by the count for that item, add up those totals, then divide by the total count.
I have tried variations on this expression:
(Account is the count) and I keep getting errors about nested aggregate functions.
Does anyone know how to do this?