cancel
Showing results 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

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!

4 REPLIES 4
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

 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

Community Support

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

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

@ 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...

Announcements

#### 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 Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors