cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AshleyMartinez Frequent Visitor
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])

 

YearDateKPIAvergKPI
2014Ene-144,0684,068
2014Feb-144,3684,368
2014Mar-144,5904,590
2014Abr-144,0254,025
2014May-144,9534,953
2014Jun-144,0174,017
2014Jul-144,4834,483
2014Ago-144,1744,174
2014Set-144,4514,451
2014Oct-144,6524,652
2014Nov-144,5004,500
2014Dic-143,3343,334
2015Ene-152,7222,722
2015Feb-152,9642,964
2015Mar-153,1623,162
2015Abr-153,0093,009
2015May-153,4993,499
2015Jun-153,9093,909
2015Jul-153,5123,512
2015Ago-154,1664,166
2015Set-154,4394,439
2015Oct-153,7943,794
2015Nov-154,2544,254
2015Dic-153,3193,319
2016Ene-164,0864,086
2016Feb-163,8113,811
2016Mar-163,3893,389
2016Abr-163,7963,796
2016May-163,4323,432
2016Jun-163,3023,302
2016Jul-164,1384,138
2016Ago-163,8583,858
2016Set-163,7743,774
2016Oct-163,8733,873
2016Nov-163,9983,998
2016Dic-164,3614,361

 

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

 

Captura.JPGExcel

Thank you in advance!

3 REPLIES 3
Super User
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
Community Support Team

Re: Average of a measure by year

Hi AshleyMartinez,

 

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

CatarinaPV Frequent Visitor
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 

 

DateSalesYearly cumulativeAverage yearly cumulative
jan-181010 
fev-182030 
mar-183060 
abr-1840100 
mai-1850150 
jun-1860210 
jul-1870280 
ago-1880360 
set-1890450 
out-18100550 
nov-18101651 
dez-18102753 
jan-19103103 
fev-19104207 
mar-19105312 
abr-19106418 
mai-19107525 
jun-19108633 
jul-19109742 
ago-19110852 
set-19111963 
out-191121075 
nov-191131188 
dez-191141302