My pivot table contains the following fields:
Owner, Balance
My formula is as follows to calculate the over 100,000 balance by owner as follows:
Over100,00bal= balance - min(100,000, balance) so it returns zero if the balance is <= 100,000.
Works fine in the listing for each owner, each of their balances are combined, then the formula gives their over 100,000 balance. However in the "TOTAL SUM OF Over100,00bal" field at the bottom of the table, instead of the sum of each of the individual "Over100,00bal" fields, it is instead applying the formula to the sum of the balances. So the result is the sum of all balances minus 100,000.
Any ideas on how to adjust my formula or pivot table to get the desired result in the Total sum field as well as the individual owner fields?
Thank you in advance
Bookmarks