How to take average of averages in ssrs

Question

I have to take average of averages in my report.

Earlier quarterly average was taken using sum of all values in a quarter divided by number of days in that quarter. Now, I need to take average of monthly averages as the quarterly average.

How shall I do it?

Answer 1

I don't think this is possible in SSRS. Problem you have is that although you can specify a scope for an aggregation, you cannot specify two scopes.

What you need to tell SSRS to do is something like this

-- THIS WILL NOT WORK
=AVG(AVG(Fields!myField.Value, "MonthRowGroupName"), "ToCurrencyColumnGroupName")

As I said, SSRS does not allow this so I typically just do this kind of thing in the dataset query as it's quite simple.

Below is some sample data which should roughly match what you have..

DROP TABLE IF EXISTS #t
CREATE TABLE #t (Q int, M int, D int, CurF varchar(10), CurT varchar(10), XR float)

INSERT INTO #t VALUES (1,1,1, 'USD', 'EUR', 1.2), (1,1,2, 'USD', 'EUR', 1.25), (1,1,3, 'USD', 'EUR', 1.28), (1,1,1, 'USD', 'GBP', 1.1), (1,1,2, 'USD', 'GBP', 1.15), (1,1,3, 'USD', 'GBP', 1.18),
 (1,2,1, 'USD', 'EUR', 1.3), (1,3,2, 'USD', 'EUR', 1.35), (1,2,3, 'USD', 'EUR', 1.38), (1,2,1, 'USD', 'GBP', 1.4), (1,2,2, 'USD', 'GBP', 1.45), (1,2,3, 'USD', 'GBP', 1.48),
 (1,3,1, 'USD', 'EUR', 1), (1,3,2, 'USD', 'EUR', 1), (1,3,3, 'USD', 'EUR', 1), (1,3,1, 'USD', 'GBP', 1), (1,3,2, 'USD', 'GBP', 1), (1,3,3, 'USD', 'GBP', 1),
 (2,1,1, 'USD', 'EUR', 1.3), (2,1,2, 'USD', 'EUR', 1.35), (2,1,3, 'USD', 'EUR', 1.38), (2,1,1, 'USD', 'GBP', 1.4), (2,1,2, 'USD', 'GBP', 1.45), (2,1,3, 'USD', 'GBP', 1.48),
 (2,2,1, 'USD', 'EUR', 1.5), (2,3,2, 'USD', 'EUR', 1.55), (2,2,3, 'USD', 'EUR', 1.58), (2,2,1, 'USD', 'GBP', 1.5), (2,2,2, 'USD', 'GBP', 1.55), (2,2,3, 'USD', 'GBP', 1.58),
 (2,3,1, 'USD', 'EUR', 1.7), (2,3,2, 'USD', 'EUR', 1.75), (2,3,3, 'USD', 'EUR', 1.8), (2,3,1, 'USD', 'GBP', 1.85), (2,3,2, 'USD', 'GBP', 1.9), (2,3,3, 'USD', 'GBP', 1.95),
 (1,1,1, 'PES', 'EUR', 21.2), (1,1,2, 'PES', 'EUR', 21.25), (1,1,3, 'PES', 'EUR', 21.28), (1,1,1, 'PES', 'GBP', 21.1), (1,1,2, 'PES', 'GBP', 21.15), (1,1,3, 'PES', 'GBP', 21.18),
 (1,2,1, 'PES', 'EUR', 21.3), (1,3,2, 'PES', 'EUR', 21.35), (1,2,3, 'PES', 'EUR', 21.38), (1,2,1, 'PES', 'GBP', 21.4), (1,2,2, 'PES', 'GBP', 21.45), (1,2,3, 'PES', 'GBP', 21.48),
 (1,3,1, 'PES', 'EUR', 21), (1,3,2, 'PES', 'EUR', 21), (1,3,3, 'PES', 'EUR', 21), (1,3,1, 'PES', 'GBP', 21), (1,3,2, 'PES', 'GBP', 21), (1,3,3, 'PES', 'GBP', 21),
 (2,1,1, 'PES', 'EUR', 21.3), (2,1,2, 'PES', 'EUR', 21.35), (2,1,3, 'PES', 'EUR', 21.38), (2,1,1, 'PES', 'GBP', 21.4), (2,1,2, 'PES', 'GBP', 21.45), (2,1,3, 'PES', 'GBP', 21.48),
 (2,2,1, 'PES', 'EUR', 21.5), (2,3,2, 'PES', 'EUR', 21.55), (2,2,3, 'PES', 'EUR', 21.58), (2,2,1, 'PES', 'GBP', 21.5), (2,2,2, 'PES', 'GBP', 21.55), (2,2,3, 'PES', 'GBP', 21.58),
 (2,3,1, 'PES', 'EUR', 21.7), (2,3,2, 'PES', 'EUR', 21.75), (2,3,3, 'PES', 'EUR', 21.8), (2,3,1, 'PES', 'GBP', 21.85), (2,3,2, 'PES', 'GBP', 21.9), (2,3,3, 'PES', 'GBP', 21.95)

SELECT 
    *, QtrAvgOfMonthAvg = AVG(MonthAvg) OVER(PARTITION BY Q, CurF, CurT)
    FROM (
            SELECT DISTINCT Q, M, CurF, CurT, MonthAvg = AVG(XR) OVER(PARTITION BY Q, M, CurF, CurT)
                FROM #t
        ) MAvg

The inner query (aliased MAvg) gets the monthly averages.

The outer query then gets the quarterly average. As this Quarterly average is repeated on each row, in SSRS we can use AVG, FIRST, MAX or MIN to just get a single instance of the number.

Here's the basic report design

Note that the SUM part of [SUM(MonthAvg)] does not matter, we could have used AVG, Min, Max etc as there is only a single number present at this scope (row and column).

and this is the result