Calculate the difference between two field counts as a part of the pivot table

1

This issue is driving me mad after a couple hours and I cannot find a solution for it. This solution is close but I don't have a third parameter like year to key on.

Here is the data I have in the pivot table:

Account ID (Rows area) - The account IDs

Count of Account ID (Values area) - This is how many rows are in the original data

Count of Reconciled (Values area) - How many entries have been reconciled

Values (Columns area - Excel makes this one by itself once I drag the other fields in place)

What I want in the 3rd "column" is:

Remaining to Reconcile - The difference of Count of Account ID and Count of Reconciled.

Visual of what I want:

Account ID | Count of Account ID | Count of Reconciled | Remaining to Reconcile
ID-1234    | 500                 | 300                 | 200

I can do the math next to the pivot table but then I cannot sort or do anything else since then you will get an error, so this is not a valid solution.

Shawn

Posted 2018-01-31T19:16:01.020

Reputation: 465

What is your data source? Do you have the ability to modify the source? The ideal solution that comes to mind is a simple boolean flag for reconciled T/F that you can use for your columns. Then you can sort/filter all you like without any trouble. – dav – 2018-01-31T20:22:48.503

Better attach the Worksheet, help us to understand the Data illustration. – Rajesh S – 2018-02-01T07:34:37.947

No answers