Months in which sessions exceed average sessions for year

Question

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.

Please help.

---

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

Answer 1

Hi,

Answered at this link

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

Answer 2

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?