Pivot Table

[gdavey]

Hi All

I would like some help with a pivot table below. I would like to reflect Tenant and rent as a percentage of the Lease id count for each area ?


[TABLE="width: 551"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Area[/TD]
[TD]Count of LeaseDealID[/TD]
[TD]Count of Tenant[/TD]
[TD]Count of Rent[/TD]
[/TR]
[TR]
[TD]Aberdeen[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Berkshire & North Hampshire[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD]Birmingham[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]Brighton[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Bristol[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Buckinghamshire[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Cambridge[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Cardiff[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]Central Scotland[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[macattackpro]

Hi Davey,

I think you're looking for a Calculated Field in the pivot table. When you insert it the easiest way to build it is to double-click your Tenant field, type in a forward slash "/", and double-click your Lease ID field. Once you click OK you may need to format the newly created calculated field to show as percentage but it should work correctly.

It'd look something like this:

='Tenant'/'Lease ID'

 

[gdavey]

Hi Thanks for this.

I tried adding the calculated field and just keep returning 0

Hi Davey,

I think you're looking for a Calculated Field in the pivot table. When you insert it the easiest way to build it is to double-click your Tenant field, type in a forward slash "/", and double-click your Lease ID field. Once you click OK you may need to format the newly created calculated field to show as percentage but it should work correctly.

It'd look something like this:

='Tenant'/'Lease ID'

 

[macattackpro]

Hi,

I see that it's counting text fields. Doing a Count formula in a calculated field won't work as Excel treats it as a Sum.

One workaround is to add a "1" to a new column in your data table for any row where Tenant is not blank and do the same for the Lease ID.

If you'd like to use a calculated field you can still do this in a similar manner by doing something like =IF('Tenant'<>"",1,0) and similar for Lease ID then once more to divide those two new fields.

Seems a bit of work but it should do the job unless someone else has a more efficient idea.