Annual Average Calculations

Question

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.&nbsp;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)&nbsp;Étiquettes de lignes200920102011201220132014201520162017Total généralAsie Pacifique200001000015000200002000020000200002000020000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 165 000&nbsp;&nbsp;&nbsp;In advance thank you for your help !&nbsp;Cécile&nbsp;&nbsp;

v-chuncz-msft · Accepted Answer

&nbsp;
CecileF,
&nbsp;
You may try the measure below.
Measure =
IF (
    HASONEFILTER ( Table1[year] ),
    SUM ( Table1[value] ),
    DIVIDE ( SUM ( Table1[value] ), COUNTROWS ( ALL ( Table1[year] ) ) )
)

&nbsp;

Forum Discussion

Annual Average Calculations

2 Replies

Related content

Annual average from monthly average

DAX Measure for Ranking the valid rows and calculating the average rating

Calculating Weighted Average

Calculating average 12 month average

Calculation of the annual average from the monthly average

É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