cancel
Showing results for
Did you mean:
Frequent Visitor

## Average of a measure by year

Hi:

I want to calculate the average of a measure by a period of time, in this case by year.

I've created these measures:

Qty = SUM(Table1[Qty])

Sells = CALCULATE(SUM(Table2[SellsInUSD])*3.2)

MovingSells = CALCULATE([Sells];DATESINPERIOD(Calendar[Date];LASTDATE(Calendar[Date]);-1;YEAR))

KPI = [Qty]/[MovingSells]

So, the KPI measure uses other ones that come from different tables (Table1, Table2).

I tried this code but the result is just the same values of "KPI":

AvergKPI = AVERAGEX(VALUES(Calendar[Year];[KPI])

 Year Date KPI AvergKPI 2014 Ene-14 4,068 4,068 2014 Feb-14 4,368 4,368 2014 Mar-14 4,590 4,590 2014 Abr-14 4,025 4,025 2014 May-14 4,953 4,953 2014 Jun-14 4,017 4,017 2014 Jul-14 4,483 4,483 2014 Ago-14 4,174 4,174 2014 Set-14 4,451 4,451 2014 Oct-14 4,652 4,652 2014 Nov-14 4,500 4,500 2014 Dic-14 3,334 3,334 2015 Ene-15 2,722 2,722 2015 Feb-15 2,964 2,964 2015 Mar-15 3,162 3,162 2015 Abr-15 3,009 3,009 2015 May-15 3,499 3,499 2015 Jun-15 3,909 3,909 2015 Jul-15 3,512 3,512 2015 Ago-15 4,166 4,166 2015 Set-15 4,439 4,439 2015 Oct-15 3,794 3,794 2015 Nov-15 4,254 4,254 2015 Dic-15 3,319 3,319 2016 Ene-16 4,086 4,086 2016 Feb-16 3,811 3,811 2016 Mar-16 3,389 3,389 2016 Abr-16 3,796 3,796 2016 May-16 3,432 3,432 2016 Jun-16 3,302 3,302 2016 Jul-16 4,138 4,138 2016 Ago-16 3,858 3,858 2016 Set-16 3,774 3,774 2016 Oct-16 3,873 3,873 2016 Nov-16 3,998 3,998 2016 Dic-16 4,361 4,361

This is what I normally do in Excel using the AVERAGEIF function, updating the average columns manually:

Excel

Thank you in advance!

3 REPLIES 3
Super User

## Re: Average of a measure by year

This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Community Support Team

## Re: Average of a measure by year

Based on your description, you want to aggregate a measure, right?

Please use nested aggregation and refer to case below:

https://community.powerbi.com/t5/Desktop/Two-level-aggregation-using-DAX-measures/td-p/182892

Regards,

Jimmy Tao

Frequent Visitor

## Re: Average of a measure by year

Hello

I am having two issues. I hope you can help me with.

I have my data and I want to create two measures:

1) Yearly Cumulative - only cumulate values for this year, as in my table.

2) Average Yearly cumulative -  I want to do an average like below:

January average: Yearly cumulative/nb months of year= 10/1

March average:Yearly cumulative/nb months of year= 60/3

...

Can you help me please?

Thank you very much

 Date Sales Yearly cumulative Average yearly cumulative jan-18 10 10 fev-18 20 30 mar-18 30 60 abr-18 40 100 mai-18 50 150 jun-18 60 210 jul-18 70 280 ago-18 80 360 set-18 90 450 out-18 100 550 nov-18 101 651 dez-18 102 753 jan-19 103 103 fev-19 104 207 mar-19 105 312 abr-19 106 418 mai-19 107 525 jun-19 108 633 jul-19 109 742 ago-19 110 852 set-19 111 963 out-19 112 1075 nov-19 113 1188 dez-19 114 1302