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 ?

Area	Count of LeaseDealID	Count of Tenant	Count of Rent
Aberdeen	9	8	5
Berkshire & North Hampshire	69	66	42
Birmingham	58	57	16
Brighton	4	4	1
Bristol	40	38	12
Buckinghamshire	15	15	6
Cambridge	16	16	6
Cardiff	28	28	13
Central Scotland	13	13	3

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[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.