I have a table of Average Number of Family Doctor Visits by Year for my Province across years as well as the Average across years for several communities within the province. Here's what the data looks like:
What I want to do now is compare each community to the province and create an index/ratio value that tells you whether the community has more or less visits on average compared to the province. The calculation, for each year would be the Average Visits for CommunityAverage Visits for Province. So for 2008, Community 1, the calculation would be 5.3/4.3 = 1.23. So for Community 1 in 2008, they have an average of about 23% more GP Visits per person for that year. This calculation needs to be done across all rows. If this can be done, what I would also like to be able to do is create some sort of parameter where the user can select the Average Provincial rate and compare it to the other communities across years. For example, lets say the user wants to select the Province value for 2012 as the gold standard and compare all the communities across years to that one static rate which would be 4.8.
Anyway, hope this makes sense. The names of the dimension columns are: GEOGRAPHY_NAME, FISCAL_YEAR. The name of the measure column is AVERAGE_GP_VISITS.