# Calculating Average of a measure based on user selection of month

• ### Question

• Hi,

I have a measure 'Assignment Count' and a time dimension Reporting Period. The Reporting Period month is used as a filter. We are using PerformancePoint.

Now, we are displaying the Assignment Count for the last 12 months based on the month selected in the filter. So, we are using <<sourcevalue>>.lag(11):<<sourcevalue>> in the Connection Formula and we are displaying the Assignment Count for the last 12 months based on the selected month which is working fine. We also have a requirement to display the Average of Assignment Count over this 12 months period. So, the set to be used in the Average Calculation is dependent on the month the user selects in the filter. It would display a straight line as the Average over the 12 months period is the same value for each of those 12 months. Can anyone please help how we can implement this ?

Thanks,

Indrashish

Thursday, April 19, 2012 4:38 AM

• I was able to achieve it by using a parameter in the report. The filter selection value is passed to the parameter and I used the parameter in the MDX Query for the report as below :

Avg({Date.Date Hierarchy.<<parameter>>.lag(11):Date.Date Hierarchy.<<parameter>>},[Measures].[Assignment Count]).

Sunday, April 29, 2012 3:02 PM

### All replies

• Have you tried creating an Average measure and using both your measures in the report? The average would be calculated depending on the time slice you pass on.

http://dailyitsolutions.blogspot.com/

Thursday, April 19, 2012 6:47 PM
• Yes, I tried but the time slice on which the average would be calculated is dependent on the month selected in the filter. It should calculate the average for the month selected in the filter and its 11 previous months and all these 12 months are displayed in the x-axis of the report. How do I calculate the average on a set dependent on the selected month in the filter ?
Thursday, April 19, 2012 7:48 PM
• Maybe I am not understanding you correctly. You need to show average for each month or cumulative average of all 12 months based upon the filter selected.

If would help if you could explain with some sample data.

http://dailyitsolutions.blogspot.com/

• Edited by Thursday, April 19, 2012 8:38 PM
Thursday, April 19, 2012 8:38 PM
• I need to show the average of 12 months based upon the filter selected not the average for each month.

For example, user selects March 2012 in the filter.

The Assignment Count for the last 12 months from the cube is provided below :

April 2011 - 35, May 2011 - 60, June 2011 - 38, July 2011 - 53, August 2011 - 54, September 2011 - 40, October 2011 - 46, November 2011 - 51, December 2011- 36, January 2012 - 48, February 2012 - 41, March 2012 - 38.

So, it should display the average of these 12 months in the report which is 45.

Friday, April 20, 2012 3:52 AM
• I was able to achieve it by using a parameter in the report. The filter selection value is passed to the parameter and I used the parameter in the MDX Query for the report as below :

Avg({Date.Date Hierarchy.<<parameter>>.lag(11):Date.Date Hierarchy.<<parameter>>},[Measures].[Assignment Count]).

Sunday, April 29, 2012 3:02 PM