Months in which sessions exceed average sessions for year

# Months in which sessions exceed average sessions for year

• Monday, March 11, 2013 1:34 AM

Hi,

I have financial years in the column area of my Pivot Table.  The following calculated Field formula return the average number of sessions conducted in each financial year

`=AVERAGEX(VALUES(Calendar1[MonthKey]),[Sessions conducted])`

The result of this formula is correct - so no problem so far.

I now wish to compute the "Number of months (in each financial year) in which the sessions conducted exceeded the average number of session".  I tried the following calculated Field formula but nothing is returned in the Pivot Table

`=COUNTROWS(FILTER(SUMMARIZE(Feedback,Calendar1[FiscalYearKey],Calendar1[MonthKey],"Sessions_per_month",[Sessions conducted]),[sessions_per_month]>[Average sessions conducted]))`

There is no error in the formula (as confirmed by the Check formula message) but no data is returned in the Pivot Table.

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

### All Replies

• Tuesday, March 12, 2013 2:24 AM

Hi,

http://www.mrexcel.com/forum/powerpivot-questions/690509-months-sessions-exceed-average-sessions-year.html

Hope this helps.

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

• Wednesday, March 13, 2013 3:59 PM

You mention "calculated field", can I assume its measure?

If it is a measure, how does it work? VALUES(Calendar1[MonthKey]) returns all the distinct MonthKey values. I would believe there should be no column [Sessions Conducted] to be summarized, how can this work? Can you please explain?