Adding a Pivot calulation field – Subtracting a Max column from a Sum column

Hi all

I am looking to subtract one pivot table column from another, but the issue I currently have is that one columns value field must be the SUM of all the items and the other must be fielded as the MAX. 

when I try and create this it returns numbers that don’t make sense to me. Here is a screenshot:

 

  Data      
YDE Code Sum of Sales MTD Max of HJD SOH Sum of Final Returns Sum of Final Replen
0 0 0 0 0
114.5305.A 0 42 0 3
114.5307.A 0 18 0 3
114.5308.A 0 44 0 3
114.5318.A 0 46 0 2
114.5319.A 0 10 0 2
114.5320.A 0 18 0 2
114.5321.A  0 187 0 5
114.5328.A 0 45 0 3
114.5336.A 1 37 0 2
114.5925.A 0 18 0 2
114.5939.A 2 125 0 3
114.5966.A 1 37 0 0
114.6120.A  0 396 0 105
114.6357.A 0 31 0 0
114.7121.A 25 104 1 62
114.7122.A 42 101 1 73
114.7123.A 1 128 0 127
114.7124.A 17 126 1 62
114.7129.A 8 19 23 1
114.7130.A 8 62 16 0

 

 

  Data        
YDE Code Sum of Sales MTD Max of HJD SOH Sum of Final Returns Sum of Final Replen Sum of SOH after Replen
0 0 0 0 0 0
114.5305.A 0 42 0 3 123
114.5307.A 0 18 0 3 51
114.5308.A 0 44 0 3 129
114.5318.A 0 46 0 2 182
114.5319.A 0 10 0 2 48
114.5320.A 0 18 0 2 34
114.5321.A  0 187 0 5 1117
114.5328.A 0 45 0 3 132
114.5336.A 1 37 0 2 183
114.5925.A 0 18 0 2 88
114.5939.A 2 125 0 3 747
114.5966.A 1 37 0 0 111
114.6120.A  0 396 0 105 8211
114.6357.A 0 31 0 0 31
114.7121.A 25 104 1 62 11794

 

By: Kevin

Leave a Reply

Your email address will not be published. Required fields are marked *