How to do an average of average?

[doume06]

Hi,

I have the following data set.

First column : Population end of month

2nd column : Population beginning of month which is the population end of month of the previous month

calculated this way:
M Population beginning of month = CALCULATE(SUM('Feuil1'[Population end of Month]),DATEADD('Feuil1'[Year/Month],-1,MONTH))
3rd column: Monthly average which is the ('Population beginning of month' + 'population end of month')/2
Calculated this way:
M Monthly average = ('Mesures'[M Population beginning of month]+SUM(Feuil1[Population end of Month]))/2
Now I want to do an average of these Monthly averages on 6 or 12 months but Power BI does not seem to like a measure of a measure.

Any hints ?
I tried:
M average of average 6 months = CALCULATE(
AVERAGE([M Monthly average]),
DATESINPERIOD('Feuil1'[Year/Month],LASTDATE('Feuil1'[Year/Month]),-6,MONTH))
No success

Thanks a lot for your help in advance
Dom

[PietroFarias]

Try to use AVERAGEX. Something like:

Measure =
AVERAGEX (
    SUMMARIZE (
        tablePopulation,
        dcalendar[periodPopulation],
        "avgPopulation", measure_avgPopulation
    ),
    [avgPopulation]
)

[amitchandak]

@doume06 , Not very clear

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

refer if this can help

https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013

[doume06]

Here is my excel file (RAW data)

http://www.dominiquemassot.fr/tmp/raw_data.xlsx

or the PBIX:

http://www.dominiquemassot.fr/tmp/Average_of_average2.pbix

Thanks

Dom

[parry2k]

@doume06 is this what you are looking for

if yes here is the measure

Measure = AVERAGEX ( VALUES ( Feuil1[Year/Month] ), [M Monthly average] )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[doume06]

Hi @parry2k,

No actually this column, I can already calculate it.

What I need is the average on 6 months of values 650 + 750 + 850 + 950 + 1050 + 1150 = 900

Like here:

Like here in Excel

MOYENNE is AVERAGE

[parry2k]

@doume06 Hi there, the solution is attached. 

JAs a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-ca...

[doume06]

Thanks @parry2k I will try that.