DAX Formula for budget measurement

[tbobolz]

Excel 2010

Hi all,
I have a PowerPivot with two databases loaded. I is the basic data set with patient detail by hospital, department, number of cases, year and month, etc. The other is a budget for these same items.


Simiplified
DB1


Hospital : Department : # Cases : Year : Month : Budgeted Cases(from DB2)
North : ED : 1 : 2014 : 1 : 20
East : ED : 1 : 2014 : 1 : 25
...

DB2

Hospital : Department: Month : Year : Budget Cases
North : ED : 1 : 2014 : 20
East : ED : 1 : 2014 : 25

While the above examples are simplified, hopefully they display the structure well. I have a pivot the calculates my various case volume by Department and I used the below formula to bring in the related budgeted case from DB2 and everything works well. This pivot is set up for a monthly display of one month at a time; however, when I update the pivot to include the sum of two or more months, then my Budgeted Case value is not longer accurate.


=CALCULATE([Budgeted Cases 2]/[Cases], 'EPSI SOAR MNDOG'[DISCHARGE - FISCAL YEAR]=2014)

I under stand that this is averaging all month and that is the reason it is wrong, but I need to kno w how to...

have the formaula find the average of each month's total THEN add then together.

The Pivot table has slicer the choose the months included in the pivot.

Thanks for any help you may offer.

Terry