I have a spreadsheet in which the data shows multiple records for a single account - each separate row shows a payment amount tied to a date. Each record also shows a summation of a different payment amount for that contract. I need the pivot table to sum the unique values associated with each record, but then I need to pull in the summary amount into the pivot table as well (without summing each of the values). For example, if I have 5 rows (which are idential records) with $100 each in the unique payment field and the summary payment amount shows $250, I want the pivot table to show for that record - a sum of $500 for the unique payment field and a sum of $250 for the other payment amount. Does anyone have any way to do this through the pivot table effectively. I cannot add columns and formulas to the data set because it is much too large and takes far too long for the calculations.
Pivot Tables - multiple rows with unique values and aggregate values
- Thread starter BSmith
- Start date
I'm not sure how slicers would be of any help here. I need the pivot table to show a sum of field 1 but then show basically an average of field 2. I tried to create a calculated field that takes the sum of field 2 divided by the count of the number of records, because I cannot use a straight average becuase the records with multiple 0's cause my total average to be much less than it should be.