cancel
Showing results for
Did you mean:
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

1 ACCEPTED SOLUTION
Super User

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

Proud to be a Super User!

3 REPLIES 3
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:

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

Proud to be a Super User!

Regular Visitor

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

Super User

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

Proud to be a Super User!

Announcements

#### The Power BI Community Show

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

#### Charticulator Design Challenge

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

#### Check it out!

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