Pivot Table Calculated Field with Count

[jschena]

Hello,

I am trying to calculate a ratio for Hours per Ticket by month. I have a pivot table that COUNTS tickets and SUMS hours per month. I was hoping to create a calculated field to the jist of COUNT(Tickets) / SUM(Hours), but this does not work because calculated fields aggregate data.... I was hoping to keep it all in a pivot table so that I can still use slicers and such. Any ideas?

Pivot Table:

Rows: Years, Months
Values: Tickets (Count), Hours (Sum)
[TABLE="width: 505"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Tickets (count)[/TD]
[TD]Hours(sum)[/TD]
[TD]Throughput (Calculated)[/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Apr[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]2.97[/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD="align: right"]311[/TD]
[TD="align: right"]321[/TD]
[TD="align: right"]0.97[/TD]
[/TR]
[TR]
[TD]Jun[/TD]
[TD="align: right"]306[/TD]
[TD="align: right"]469[/TD]
[TD="align: right"]0.65[/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD="align: right"]415[/TD]
[TD="align: right"]329[/TD]
[TD="align: right"]1.26[/TD]
[/TR]
[TR]
[TD]Aug[/TD]
[TD="align: right"]461[/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]1.32[/TD]
[/TR]
[TR]
[TD]Sep[/TD]
[TD="align: right"]430[/TD]
[TD="align: right"]434[/TD]
[TD="align: right"]0.99[/TD]
[/TR]
[TR]
[TD]Oct[/TD]
[TD="align: right"]538[/TD]
[TD="align: right"]428[/TD]
[TD="align: right"]1.26[/TD]
[/TR]
[TR]
[TD]Nov[/TD]
[TD="align: right"]458[/TD]
[TD="align: right"]416[/TD]
[TD="align: right"]1.10[/TD]
[/TR]
[TR]
[TD]Dec[/TD]
[TD="align: right"]420[/TD]
[TD="align: right"]401[/TD]
[TD="align: right"]1.05[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD="align: right"]401[/TD]
[TD="align: right"]374[/TD]
[TD="align: right"]1.07[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]358[/TD]
[TD="align: right"]1.12[/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD="align: right"]458[/TD]
[TD="align: right"]443[/TD]
[TD="align: right"]1.03[/TD]
[/TR]
[TR]
[TD]Apr[/TD]
[TD="align: right"]385[/TD]
[TD="align: right"]457[/TD]
[TD="align: right"]0.84[/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD="align: right"]370[/TD]
[TD="align: right"]260[/TD]
[TD="align: right"]1.42[/TD]
[/TR]
[TR]
[TD]Jun[/TD]
[TD="align: right"]203[/TD]
[TD="align: right"]185[/TD]
[TD="align: right"]1.10[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]5621[/TD]
[TD="align: right"]5247[/TD]
[TD="align: right"]1.07[/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]



Thank you thank you!!

 

[Excelaron]

Did you try to put the calculation in the source data?