Need Pivot Table help

[naven_sg]

Hello Need a help in Pivot table.

Using the below raw data, i have created Pivot table which shows how many "calls", how many "Made" and "Miss". I also need SLA achived (Made/Miss), can I calculated this(SLA achived) in the pivot table it self.

Note: SLA Achived column given below is not part of pivot. What i really want is this possible to include this in the Pivot.

Regards,
NG

Raw Data</SPAN>						Required data</SPAN>
Ticket</SPAN>	City</SPAN>	SLA</SPAN>
1654</SPAN>	CHESAPEAKE</SPAN>	Made</SPAN>
1655</SPAN>	COOKEVILLE</SPAN>	Miss</SPAN>				City	Made	Miss	Grand Total	SLA Achieved</SPAN>
1656</SPAN>	IRVING</SPAN>	Made</SPAN>				CHESAPEAKE</SPAN>	2</SPAN>	1</SPAN>	3</SPAN>	67%</SPAN>
1657</SPAN>	KALAMAZOO</SPAN>	Made</SPAN>				COOKEVILLE</SPAN>	2</SPAN>	1</SPAN>	3</SPAN>	67%</SPAN>
1658</SPAN>	LAWRENCE</SPAN>	Made</SPAN>				IRVING</SPAN>	3</SPAN>		3</SPAN>	100%</SPAN>
1659</SPAN>	LYNCHBURG</SPAN>	Made</SPAN>				KALAMAZOO</SPAN>	3</SPAN>		3</SPAN>	100%</SPAN>
1660</SPAN>	MOOSIC</SPAN>	Made</SPAN>				LAWRENCE</SPAN>	3</SPAN>		3</SPAN>	100%</SPAN>
1661</SPAN>	RALEIGH</SPAN>	Made</SPAN>				LYNCHBURG</SPAN>	3</SPAN>		3</SPAN>	100%</SPAN>
1662</SPAN>	SPRINGVILLE</SPAN>	Made</SPAN>				MOOSIC</SPAN>	2</SPAN>	1</SPAN>	3</SPAN>	67%</SPAN>
1663</SPAN>	SULPHUR SPRI</SPAN>	Miss</SPAN>				RALEIGH</SPAN>	2</SPAN>	1</SPAN>	3</SPAN>	67%</SPAN>
1664</SPAN>	TANEYTOWN</SPAN>	Miss</SPAN>				SPRINGVILLE</SPAN>	2</SPAN>	1</SPAN>	3</SPAN>	67%</SPAN>
1665</SPAN>	VERNON</SPAN>	Miss</SPAN>				SULPHUR SPRI</SPAN>	1</SPAN>	2</SPAN>	3</SPAN>	33%</SPAN>
1666</SPAN>	CHESAPEAKE</SPAN>	Miss</SPAN>				TANEYTOWN</SPAN>	1</SPAN>	2</SPAN>	3</SPAN>	33%</SPAN>
1667</SPAN>	COOKEVILLE</SPAN>	Made</SPAN>				VERNON</SPAN>	1</SPAN>	2</SPAN>	3</SPAN>	33%</SPAN>
1668</SPAN>	IRVING</SPAN>	Made</SPAN>				Grand Total	25	11	36	69%</SPAN>
1669</SPAN>	KALAMAZOO</SPAN>	Made</SPAN>
1670</SPAN>	LAWRENCE</SPAN>	Made</SPAN>
1671</SPAN>	LYNCHBURG</SPAN>	Made</SPAN>
1672</SPAN>	MOOSIC</SPAN>	Made</SPAN>
1673</SPAN>	RALEIGH</SPAN>	Made</SPAN>
1674</SPAN>	SPRINGVILLE</SPAN>	Made</SPAN>
1675</SPAN>	SULPHUR SPRI</SPAN>	Miss</SPAN>
1676</SPAN>	TANEYTOWN</SPAN>	Miss</SPAN>
1677</SPAN>	VERNON</SPAN>	Miss</SPAN>
1678</SPAN>	CHESAPEAKE</SPAN>	Made</SPAN>
1679</SPAN>	COOKEVILLE</SPAN>	Made</SPAN>
1680</SPAN>	IRVING</SPAN>	Made</SPAN>
1681</SPAN>	KALAMAZOO</SPAN>	Made</SPAN>
1682</SPAN>	LAWRENCE</SPAN>	Made</SPAN>
1683</SPAN>	LYNCHBURG</SPAN>	Made</SPAN>
1684</SPAN>	MOOSIC</SPAN>	Miss</SPAN>
1685</SPAN>	RALEIGH</SPAN>	Miss</SPAN>
1686</SPAN>	SPRINGVILLE</SPAN>	Miss</SPAN>
1687</SPAN>	SULPHUR SPRI</SPAN>	Made</SPAN>
1688</SPAN>	TANEYTOWN</SPAN>	Made</SPAN>
1689</SPAN>	VERNON</SPAN>	Made</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL span=4><COL><COL><COL><COL><COL></COLGROUP>

 

[Vidar]

Hi
You can add the "SLA" field to the value area of the pivot table one more time and choose "show values as" "% of row total".

Vidar

 

[naven_sg]

Hello Vidar,

I have already tried this. The result will show like below; I just need Made%, but this will also give Miss% and Total% (Which i dont need). Also you may suggent to hide the columns which you dont need. But if there is no misses for that particular day then an incorrect column get hidden.

	Column Labels
	Count of Ticket		Count of SLA		Total Count of Ticket	Total Count of SLA
Row Labels	Made	Miss	Made	Miss
CHESAPEAKE</SPAN>	2</SPAN>	1</SPAN>	66.67%</SPAN>	33.33%</SPAN>	3</SPAN>	100.00%</SPAN>
COOKEVILLE</SPAN>	2</SPAN>	1</SPAN>	66.67%</SPAN>	33.33%</SPAN>	3</SPAN>	100.00%</SPAN>
IRVING</SPAN>	3</SPAN>		100.00%</SPAN>	0.00%</SPAN>	3</SPAN>	100.00%</SPAN>
KALAMAZOO</SPAN>	3</SPAN>		100.00%</SPAN>	0.00%</SPAN>	3</SPAN>	100.00%</SPAN>
LAWRENCE</SPAN>	3</SPAN>		100.00%</SPAN>	0.00%</SPAN>	3</SPAN>	100.00%</SPAN>
LYNCHBURG</SPAN>	3</SPAN>		100.00%</SPAN>	0.00%</SPAN>	3</SPAN>	100.00%</SPAN>
MOOSIC</SPAN>	2</SPAN>	1</SPAN>	66.67%</SPAN>	33.33%</SPAN>	3</SPAN>	100.00%</SPAN>
RALEIGH</SPAN>	2</SPAN>	1</SPAN>	66.67%</SPAN>	33.33%</SPAN>	3</SPAN>	100.00%</SPAN>
SPRINGVILLE</SPAN>	2</SPAN>	1</SPAN>	66.67%</SPAN>	33.33%</SPAN>	3</SPAN>	100.00%</SPAN>
SULPHUR SPRI</SPAN>	1</SPAN>	2</SPAN>	33.33%</SPAN>	66.67%</SPAN>	3</SPAN>	100.00%</SPAN>
TANEYTOWN</SPAN>	1</SPAN>	2</SPAN>	33.33%</SPAN>	66.67%</SPAN>	3</SPAN>	100.00%</SPAN>
VERNON</SPAN>	1</SPAN>	2</SPAN>	33.33%</SPAN>	66.67%</SPAN>	3</SPAN>	100.00%</SPAN>
Grand Total	25	11	69.44%	30.56%	36	100.00%

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL></COLGROUP>

 

[Andrew Poulsom]

Put City in the Row area, SLA in the Column Area and Count of Ticket in the Data area. Then select the SLA field and add a Calculated Item with the formula:

=Made/(Made+Miss)

Result in Excel 2003:

Excel 2003
	F	G	H	I
1	Count of Ticket	SLA
2	City	Made	Miss	Achieved
3	CHESAPEAKE	2	1	67%
4	COOKEVILLE	2	1	67%
5	IRVING	3		100%
6	KALAMAZOO	3		100%
7	LAWRENCE	3		100%
8	LYNCHBURG	3		100%
9	MOOSIC	2	1	67%
10	RALEIGH	2	1	67%
11	SPRINGVILLE	2	1	67%
12	SULPHUR SPRI	1	2	33%
13	TANEYTOWN	1	2	33%
14	VERNON	1	2	33%
Sheet1

 

[naven_sg]

Hello Andrew,


Thanks for the solution it works perfectly fine.


Regards,
NG