Pivot Tables - Summing data items

[gbecky3]

State is in the ROW area, and Measure is in the Data Area
at the bottom I would like to see a total of Membership, and a total of Admits, so at the bottom it would read
Total Membership: 120 for (Qtr1) under the Qtr1 column
Total Admits 9 for (Qtr1)
(same thing under the Qtr2 column)

State Measure Qtr1 Qtr2

Or Membership 45 75
Admits 5 4
Wa Membership 75 80
Admits 4 23

 

[texasalynn]

is this what you want?

Excel Workbook
	A	B	C	D
1	State	Measure	Qtr1	Qtr2
2	Or	Membership	45	75
3	Or	Admits	5	4
4	Wa	Membership	75	80
5	Wa	Admits	4	23
Sheet1

Excel Workbook
	A	B	C	D	E	F	G	H	I
3	*	*	*	Measure	Data	*	*	*	*
4	*	*	*	Admits	*	Membership	*	Total Sum of Qtr1	Total Sum of Qtr2
5	State	Qtr1	Qtr2	Qtr1	Qtr2	Qtr1	Qtr2	*	*
6	Or	5	4	5	4	*	*	5	4
7	*	45	75	*	*	45	75	45	75
8	Or Total	*	*	5	4	45	75	50	79
9	Wa	4	23	4	23	*	*	4	23
10	*	75	80	*	*	75	80	75	80
11	Wa Total	*	*	4	23	75	80	79	103
12	Grand Total	*	*	9	27	120	155	129	182
Sheet4

 

[gbecky3]

That is really nice, kind of what I want, it would be nice to have the totals at the bottom instead of across.
but if you show me how to do what you did, maybe I can go from there...

I actually have six measures...including the Membership, and admits,
would look like:

Total Membership Qtr1: 120 Qtr2 155
Total Admits Qtr1: 9 Qtr2 27
Admits/K (I would need a calculation here = Admits*1000/Membership*4)
Days
Days/K (another calc here = Days * 1000/Membership*4)
ALOS (average Length of stay = Days/Admits)

 

[texasalynn]

what I did was
put the state, qtr1 & qtr2 in the row
put the measure in the column
put the qtr1 & qtr2 in the data

then I changed the report format to table10
I hide the totals for the qtr's in the row

so from there you could add your calculated fields and maybe hide some of the other things

 

[gbecky3]

I will give it a try, thanks, I can't remember how to format the pivot tho...

 

[texasalynn]

I will give it a try, thanks, I can't remember how to format the pivot tho...

What are you wanting to format?