Calculating difference between totals in different pivot tables, where some fields do not match

Jules79

New Member
Joined
Oct 27, 2016
Messages
2
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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Jules79

New Member
Joined
Oct 27, 2016
Messages
2
Apologies, my example pivot tables should say Example 2 and example 3. (The 1st pivot table would show a list of all 3 destinations and looks up all yes and no's.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,519
Messages
5,625,280
Members
416,086
Latest member
CaptainGD

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top