Annual Average Calculations

[CecileF]

Hello,

I would like to calculate an annual average of values. However the calculation made by power BI with the quick measure average by year only count the distinct years where there is a value which is not the calculation I want as I need the annual average taking into account years empty.

Please see example below :

Annual average from 2008 (empty) to 2017 made manually (Total divided by 10 years) = 165 000/10 = 16 500 whereas Power BI calculates the average of values as to know 18 333 (corresponds to 165 000/9 years with a value)

Étiquettes de lignes	2009	2010	2011	2012	2013	2014	2015	2016	2017	Total général
Asie Pacifique	20000	10000	15000	20000	20000	20000	20000	20000	20000	165 000

In advance thank you for your help !

Cécile 

[v-chuncz-msft · Accepted solution]

@CecileF,

You may try the measure below.

Measure =
IF (
    HASONEFILTER ( Table1[year] ),
    SUM ( Table1[value] ),
    DIVIDE ( SUM ( Table1[value] ), COUNTROWS ( ALL ( Table1[year] ) ) )
)

[CecileF]

Hello,

Thanks a lot for your precious help ! 

I just did a small change as I have filters in my model and changed the ALL in ALLSELECTED.

Have a great day !

Best regards

Cécile