Rolling 12 months average of existing average

[Nielf]

I am trying to create a 12 months rolling average of an already existing average. I am in doubt if that is the correct way to this statistically/mathematic and/or if Power BI takes this into account when doing the calculation.

I have simple measure that looks like this, which avereges scores in a questionnaire.

Average of all questions = 

AVERAGE('Question response'[Score - 7-point Likert scale])

I would like to show the development as a 12 months rolling average. So I have created a measure like this, which I have prevously used, and simply replaced to measure in the formula:

Average of all questions (R12M) = 
VAR NumOfMonths = 12
VAR LastSelectedDate = MAX('Date'[Date])
VAR Period = 
DATESINPERIOD('Date'[Date], LastSelectedDate, -NumOfMonths, MONTH)
VAR Result = 
CALCULATE(
    AVERAGEX(
        VALUES('Date'[Month]),
        [Average of all questions]
    ),
    Period
)
RETURN
    IF([Average of all questions] > 0, Result)

Can I use this approach or will get a wrong result by making an average of an average?

I hope it makes sense 🙃

[VijayP]

@Nielf  First you need to get average of each set and then average of those sets . Two layers of measures required. I need to check with some dummy data

[Nielf]

Thanks, @VijayP . 

I am trying do something similar but haven't yet found a solution. 

[Nielf]

I most cases, the respondents will answer 14 questions. However, the they can skip questions, so I guess the 'cardinality' is not always the same if understand that correct. 

So the questions is, how I can get around this when creating the 12 months rolling average. 

[VijayP]

@Nielf 

The average of averages is only true if all the averages are computed over sets having the same cardinality. Otherwise, it is false.

The average of averages is not the average This is a common enough mistake for people working with databases and doing number crunching. It is only true if all of the averages are computed over sets having the same cardinality, otherwise it is false.

Daniel Lemire's blog  https://lemire.me/blog/2005/10/28/average-of-averages-is-not-the-average/
To find the average of averages, you can:
Calculate the total of all the individual averages
Divide that total by the number of averages
This is known as the weighted average. For example, if you have three averages (A, B, and C), you would calculate (A + B + C) / 3 to find the average of the averages.
A more representative average can be found by weighting each subset's average by its size (number of elements) and then dividing by the total number of elements across all subsets.
According to Convalytics, taking an average of averages is wrong because it doesn't take into account how many units went into each average.
According to Steve Fenton, attempting to average existing averages without knowing the number of values contained in each value leads to statistical errors