Multiple Pivot Table Combinations

[123CJA]

Hi,

I have a data set which includes a field for "Result", two options within this field are "Lost" & "Cancelled". There is another field "Closure Rationale" which contains options which can be common for both of these "Results".

I have grouped the "Closure Rationale" into "Cancelled W" or "Cancelled W/O" so I now have a sub-group "Closure Rationale2" however I cannot work out how to drill the data so I can show a single total for:

All "Lost" Business (including "Cancelled W"AND "Cancelled W/O")
All "Cancelled" Business excluding "Cancelled W"

In other words I need my pivot table to include both "Closure Rationale2" for "Lost" but only "Cancelled W" for Result of "Cancelled".

I know I can add a column on my data which would cover this however I am sure it must be possible within the pivot table.

Sample:
Result[TABLE="width: 356"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Closure Rationale[/TD]
[/TR]
[TR]
[TD]Cancelled[/TD]
[TD]Contract Expired[/TD]
[/TR]
[TR]
[TD]Cancelled[/TD]
[TD]Replaced by Another[/TD]
[/TR]
[TR]
[TD]Lost[/TD]
[TD]Customer Merger / Acquisition / Liquidation[/TD]
[/TR]
[TR]
[TD]Cancelled[/TD]
[TD]Customer Merger / Acquisition / Liquidation[/TD]
[/TR]
[TR]
[TD]Cancelled[/TD]
[TD]Internal Portfolio Transfer[/TD]
[/TR]
</tbody>[/TABLE]

I hope this makes send and thank you in-advance for any help.

 

[123CJA]

I have taken this from my pivot table:
The TOTAL I have worked out myself however this is what I need to get too with a single cell!

[TABLE="width: 518"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Cancelled[/TD]
[TD][/TD]
[TD]Lost[/TD]
[TD][/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD]Cancelled W/O[/TD]
[TD]Cancelled W[/TD]
[TD]Cancelled W/O[/TD]
[TD]Cancelled W[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]$40.23[/TD]
[TD="align: right"]$409.83[/TD]
[TD="align: right"]$18.67[/TD]
[TD="align: right"]$3.66[/TD]
[TD="align: right"]$62.56[/TD]
[/TR]
</tbody>[/TABLE]