Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AshleyMartinez
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:

 

ExcelExcel

Thank you in advance!

4 REPLIES 4
xavirm
New Member

Hello Ashley!

How did you solved this?

I need to calculate the average for a whole year and showing it all along the year visualization (even in the coming months).

Thanks a lot.

Regards,

Anonymous
Not applicable

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 

 

v-yuta-msft
Community Support
Community Support

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

Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.