Calculated Fields in Pivot Tables

I have previously written about the power of pivot tables in Microsoft Excel, but in this article I will expand on that power.  Pivot tables are a great way to summarize data.  Sage uses them with their Intelligence Reporting tool.  Now what if you want to do some calculations on the raw data used for reports.  Say the data you have has sales by invoice, the cost of the sale and the margin on each sale.  Also, you want to make sure that every sale has at least a 30% margin.

To create a calculated field in a pivot table, all you need to do is select the pivot table and on the Excel menu ribbon you will get some additional options.  In my version of Excel 2013 under the ANALYZE tab select the fields, items and sets option and choose calculate field.  You can name your field and the formula.  In my example, I could call my field mrgpct and the formula would be =IF(sale>0, margin/sale,0).  I could then format that column to be a percentage and I would have the margin percent.  The final touch is to highlight the mrgpct field and from the HOME tab select conditional formatting, highlight cell rules, less than and enter 30% and select the default fill, light red fill with dark red text.

I have now highlighted any sales that did not meet the minimum margin.  This is just one example of how you can use calculated fields with pivot tables in Excel.  Calculated fields can really add great information to your reporting.

Written by Michael Ericksen

WAC Solution Partners Midwest

Sage Intelligence Reporting

The best tool in the Sage ERP product line is Sage Intelligence Reporting.  This tool comes with a number of preconfigured reports for financial, sales, inventory and purchasing reporting.  This article will cover the sales, inventory and purchasing reporting tool.  Sage Intelligence works with Microsoft Excel to create Pivot Tables for the reporting.  Pivot Tables are a great tool for summarizing data.

Take the Sales Master report:  This report allows you to see the sales total for a customer and the total for each item purchased.  You can easily filter the report to see the total sales for any salesperson or combination of salespeople.  You can modify the report to total on the salesperson and then the customer.  You can drop the items and just see company totals.  You also can just see the top 100 customers or maybe the top 10.  If you want to add a field not on the report you can add a column in the report specs in the Report Manager.  If the field you want to add is not in one of the tables the report uses, you can modify the container with a simple SQL join statement and then all the fields in the new table can be added to your report.

This product is available to add to Sage ERP products.  it can be paid for as a one time fee plus maintenance or you can set it up as a subscription and pay for it monthly.  Sage Intelligence Reporting is a very powerful tool that takes advantage of the capabilities of Excel Pivot Tables and the simple SQL statements to create flexible meaningful reports.

Written By Michael Ericksen, WAC Solution Partners- Midwest

-Partner, Certified Consultant