Changing/Adding a Calculated field to a Pivot Table

June 30, 2011

We created this post to assist our customers who have acquired reports that use calculated fields that may require different inputs based on the options available. At least two of the reports in our IntelligenceStore that may require this skill are the ‘Customer Rebate’ and ‘Commission on Sales/Receipts’ reports.

By way of example, the ‘Commision on Sales’ report for Simply Accounting provides the user the option of using any of the 5 available fields in the ‘Additional Info’ tab in the Employee Records screen to host the commission percentage for a salesperson. Depending on which field is used,  the calculated field in the pivot table may have to change to match the field used.

Below is an example of changing the default ‘field 1’ in the calculated field to ‘field 2’. Remember that these fields represent any one of the 5 fields hosted in the screen below in Simply Accounting.

1)  Once you have run the report, select the pivot table in Excel and select the Fileds, Items & Sets button and select Calculated Field

2) Now select CommissionDue or CommissionDueCustLevl, depending on where your Saleperson is hosted in Simply Accounting

3) Now change the fomula in the calculated field to draw from any of the other 4 fields available; in this case, the 2nd field


Your pivot table will draw the salesperson commission from the 2nd field in the ‘Additional Info’ tab in the employee records


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: