$40 TO THE FIRST ONE TO PROVIDE SOLUTION TO ME:
I have a basic Excel 2010 model that uses a PowerPivot pivottable with hospital stats (revenue and bed count)--see attached. The model uses two pivottables to show a comparison between a subject hospital and a peer group of several hospitals. Using Excel 2010 slicers, a user can select a hospital in pivottable#1 to show a subject hospital and then compare that hospital's stats to a peer group that is selected in pivottable#2. The peer group will show average of all hospitals selected.
I have a slicer that allows peer group to be narrowed by states. In addition to this slicer, I need a slicer to narrow the peer group based upon mileage from selected subject hospital. This means that the slicer must be able to dynamically change based upon whatever subject hospital is selected (see attached file). The database includes latitude and longitude for every hospital, so please use these to calculate mileage. Also, this has to work on sharepoint server, so that means this slicer must be made without the use of an excel linked table to powerpivot (excel linked tables do not dynamically update on sharepoint server).
The excel formula calculation of mileage is as follows: =ACOS(COS(RADIANS(90-Lat1))*COS(RADIANS(90-Lat2))+SIN(RADIANS(90-Lat1))*SIN(RADIANS(90-Lat2))*COS(RADIANS(Long1-Long2)))*3958.756
File upload to freelancer.com is NOT WORKING. If you are interested in project, please let me know and I will email you file.