Create a dynamic table to calculate churn rates by Department / Division etc,

[Cortlyn]

I have two sets of data from two Companies for 3 months which I have merged together by month. I have then created a table that shows the data by departmental hierarchy. I am trying to create a table that will calculate the churn rates over 12 month (May 14 to April 15) and 1 month March to April 15. (please see attached)
I would like the table (with drop downs/slicers) to calculate based on a filtered selection i.e. when a Business line is selected the calculation does the churn calculation for that Business Line and the individually for the Practice Lines that come under it and then for the Service Lines in those Practice Line and down to the Office Locations.
As some of the Business Lines/Practice Lines cross over Office locations, I would like it to work when any group is selected, i.e. when an office Location is selected it shows all the Business/ Practice Lines and visa vesa.
The churn calculation is simply the average of the number of leavers / average of start and end month headcounts.
Thanks
Cort

[Cortlyn]

I think that I was trying to do too much in one table on this one. I got around the problem by creating a pivot table (by the main Group first) and adding Calculated fields to do the calculation. Making copies of the pivot and rearranging the order of the row labels putting the group I wanted to show first in the respective pivots. I then linked the pivots with slicers, one for each group. Now when any selection is made on the slicer it automatically updates the others.

It means I have multiple small tables rather than 1 big one - it actually looks neater and I can insert charts for each one.

If you have a different way, please post how..

Thanks for taking time to look at this.