Calculated Pivot field and Slicer interaction

[MikeLiberty]

I'm going to apologize in advance if this is unclear. I'm trying to make it as concise as possible. I'm trying to create a dashboard using pivot charts with slicers to map our turnover rate by performance category and organization (limited to office/dept combination for this example but actually 5 layers deep).

Turnover is calculated as the # of terms/ Avg active headcount. I want to have a stacked bar chart that shows total turnover in a bar that is broken into types of turnover with a bar for each performance group. I then want to be able to use slicers to filter it by office and or department.

I'm not sure if I'm not putting my data into the correct layout to do this, if my pivot chart has the wrong setting or if its not possible. Here is my data table:

Excel Workbook
	D	E	F	G	H	I	J	K	L	M	N	O
16				Terms	Terms	Terms	Headcount	Headcount	Headcount	Turnover	Turnover	Turnover
17	Office	Dept Code	Term Reas	Low	Med	High	AVG Low	AVG Med	AVGHigh	TurnLow	TurnMed	TurnHigh
18	0102	05454	Vol Other	5	5	5	40	55	70	0.5%	0.3%	0.2%
19	0102	05454	Vol Work	7	2	7	40	55	70	0.7%	0.1%	0.3%
20	0102	05454	Vol Advancement	2	8	11	40	55	70	0.2%	0.5%	0.4%
21	0102	05454	Invol Other	0	3	2	40	55	70	0.0%	0.2%	0.1%
22	0102	05454	Invol RIF	0	2	0	40	55	70	0.0%	0.1%	0.0%
23	0102	05454	Invol Perf	1	0	0	40	55	70	0.1%	0.0%	0.0%
24	0102	05461	Vol Other	2	5	5	40	55	70	0.2%	0.3%	0.2%
25	0102	05461	Vol Work	4	2	8	40	99	94	0.4%	0.1%	0.3%
26	0102	05461	Vol Advancement	7	8	8	40	200	84	0.7%	0.5%	0.3%
27	0102	05461	Invol Other	6	8	0	40	55	70	0.6%	0.5%	0.0%
28	0102	05461	Invol RIF	0	6	0	40	55	70	0.0%	0.4%	0.0%
29	0102	05461	Invol Perf	1	0	0	40	55	70	0.1%	0.0%	0.0%
30	0220	06679	Vol Other	5	5	5	40	55	70	0.5%	0.3%	0.2%
31	0220	06679	Vol Work	7	2	7	40	55	999	0.7%	0.1%	0.3%
32	0220	06679	Vol Advancement	2	8	11	40	55	70	0.2%	0.5%	0.4%
33	0220	06679	Invol Other	0	3	2	40	55	70	0.0%	0.2%	0.1%
34	0220	06679	Invol RIF	0	2	0	40	55	5	0.0%	0.1%	0.0%
35	0220	06679	Invol Perf	1	0	0	40	55	70	0.1%	0.0%	0.0%
36	0220	06680	Vol Other	5	5	5	40	55	55	0.5%	0.3%	0.2%
37	0220	06680	Vol Work	7	2	7	40	55	70	0.7%	0.1%	0.3%
38	0220	06680	Vol Advancement	2	8	11	40	55	70	0.2%	0.5%	0.4%
39	0220	06680	Invol Other	0	3	2	40	55	70	0.0%	0.2%	0.1%
40	0220	06680	Invol RIF	0	2	0	40	55	70	0.0%	0.1%	0.0%
41	0220	06680	Invol Perf	1	0	0	40	55	70	0.1%	0.0%	0.0%
42			Totals	65	89	96	960	1509	2567	6.8%	5.9%	3.7%
Sheet2

Excel 2010


It works at the first level, but once i try to slice it, the turnover needs to become terms/ avg headcount for the filtered total and not the overall total. Which is where I'm running into problems