cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
icerdeira
Regular Visitor

Average of years

Hi,

 

I have made a what-if (parameter) to use the years as a parameter. When I put in the segmentation filter 2015 - 2020 it shows me the averages in an amount by years.

 

I need the average for 2015 and then the average for 2020 separately using my parameter. So far my formula only gives me the mean between 2015 and 2020 but I don't need the years 2016, 2017 and 2019. I use only 2015 and 2020 to get the ROI.

 

Thanks,

 

Isaac

icerdeira_2-1640168211822.png

 

icerdeira_1-1640168103630.png

 

icerdeira_0-1640168023725.png

 

 

1 ACCEPTED SOLUTION

In that case you could place the Initial and End averages into variables or even measures and use those in your calculations. E.g. 

AverageYears (only variables)=
var minYear =MIN('YourParameter'[Year])
var maxYear =Max('YourParameter'[Year]
var initialAve =  AVERAGEX(FILTER(AverageYears,AverageYears[Year]=minYear),AverageYears[Value])
var endAve =  AVERAGEX(FILTER(AverageYears,AverageYears[Year]=maxYear),AverageYears[Value])
return
//Now use these to get the values you want 




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

Proud to be a Super User!




View solution in original post

3 REPLIES 3
ValtteriN
Super User
Super User

Hi,

Here is one way to do this:

AverageYears =
var minYear =MIN('Calendar'[Year])
var maxYear =Max('Calendar'[Year]) return
AVERAGEX(FILTER(AverageYears,or(AverageYears[Year]=maxYear,AverageYears[Year]=minYear)),AverageYears[Value])
I used calendar instead of parameter as a slicer.

End result:

ValtteriN_0-1640170146582.png

I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!

 





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

Proud to be a Super User!




Hi, thanks for the answer.

 

The problem is that I need to save the average separately. The initial average and the final average. Then I use those 2 measures to get de ROI. The year must be taken by a segmentation filter.

 

Thanks,

 

Isaac

In that case you could place the Initial and End averages into variables or even measures and use those in your calculations. E.g. 

AverageYears (only variables)=
var minYear =MIN('YourParameter'[Year])
var maxYear =Max('YourParameter'[Year]
var initialAve =  AVERAGEX(FILTER(AverageYears,AverageYears[Year]=minYear),AverageYears[Value])
var endAve =  AVERAGEX(FILTER(AverageYears,AverageYears[Year]=maxYear),AverageYears[Value])
return
//Now use these to get the values you want 




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

Proud to be a Super User!




Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!