I have a single table of data that contains a number of different numerical/text fields against a record. They also have a calculated yes/no indicator depending on whether that record meets a particular criteria. I then have a number of pivot tables on another worksheet that calculates the number of records that meet this yes/no criteria. (COUNT) So I'll have one pivot table that pulls back 100% of my data. Pivot table 2 is adjacent to this and is filtered by where criteria 1 meets "yes". The 3rd pivot table is filtered by criteria 2 that meets "yes" and so on. Against these subsequent pivot tables I do a =F41/$C41 formula to calculate the percentage of those "yes" counts against the first pivot table that contains a count of all records. This works perfectly well where the subsequent tables contain the same fields (ie the field names are adjacent to each other) but not where they don't.
If my 1st pivot table contains 6 records, but in my 2nd pivot which is filtered by yes it only pulls back 4 records, I still want to calculate the %age, but it has to match up agasint the matching fields. I'm currently doing this by manually moving the cells within the pivot table that the formula is look at each time I refresh the data, but there has to be a smarter way of doing this. I don't know if the =F41/$C41 formula can incorporate some kind of vlookup or countif or whether I should use GETPIVOTDATA? Ideally I would like a calculated field within the pivot table put I don't know if that is possible when you have Yes/No values within the same column of data that you filter by on the pivot table. Hope that makes some kind of sense! Thank you.
EXAMPLE DATA
DEST REF SCAN DELIVERED
Birm 103 YES YES
Swin 104 YES NO
Duns 123 NO YES
PIVOT EXAMPLE 1
Scan = "Yes"
DEST
Birm 1
Swin 1
PIVOT EXAMPLE 2
Delivered = "Yes"
DEST
Birm 1
Duns 1
If my 1st pivot table contains 6 records, but in my 2nd pivot which is filtered by yes it only pulls back 4 records, I still want to calculate the %age, but it has to match up agasint the matching fields. I'm currently doing this by manually moving the cells within the pivot table that the formula is look at each time I refresh the data, but there has to be a smarter way of doing this. I don't know if the =F41/$C41 formula can incorporate some kind of vlookup or countif or whether I should use GETPIVOTDATA? Ideally I would like a calculated field within the pivot table put I don't know if that is possible when you have Yes/No values within the same column of data that you filter by on the pivot table. Hope that makes some kind of sense! Thank you.
EXAMPLE DATA
DEST REF SCAN DELIVERED
Birm 103 YES YES
Swin 104 YES NO
Duns 123 NO YES
PIVOT EXAMPLE 1
Scan = "Yes"
DEST
Birm 1
Swin 1
PIVOT EXAMPLE 2
Delivered = "Yes"
DEST
Birm 1
Duns 1
