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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
icerdeira
Frequent 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors