For NetSuite users, Saved Searches are among the most frequently used tools for reporting and analysis. However, users often run into a major issue when attempting to calculate percentage-based metrics—such as Gross Margin %—in a summary saved search.
The problem arises because NetSuite calculates percentages at the individual transaction level and does not apply the formula to the summarized totals. Instead, when a summary search is created, NetSuite requires a summary type (SUM, AVG, etc.) to be assigned to the percentage calculation, which results in incorrect values.
This affects any percentage calculation, but one of the most common examples where it leads to reporting errors is Gross Margin % calculations for sales transactions. Let’s dive into why this happens and how to generate accurate results.
The Core Issue: How NetSuite Handles Percentage Calculations in Summary Saved Searches
When a saved search is created, NetSuite requires that every calculation formula be assigned a summary type (e.g., SUM, AVG, MIN, MAX).
The issue? NetSuite does not reapply percentage formulas to the summarized totals—instead, it forces users to apply a summary type to the pre-calculated percentages, leading to incorrect results.
For example, Gross Margin % is typically calculated at the transaction level using this formula:
At the individual transaction level, this calculation is accurate. When tsummarizing data in a saved search, NetSuite requires the user to select a summary type for the Gross Margin % formula, and neither option produces a correct result.
Why Does This Cause Incorrect Results?
Let’s examine the two available summary options and why they fail.
Incorrect Option 1: Using the SUM Function
If the summary type is set to SUM, NetSuite will add up all the individually calculated Gross Margin % values, which is incorrect.
Since NetSuite sums the percentage values (40% + 40% = 80%), obviously this result is completely inaccurate.
Incorrect Option 2: Using the AVG Function
If the summary type is set to AVG, NetSuite will average all the individually calculated Gross Margin % values, rather than applying the correct formula to the summarized totals.
The correct overall Gross Margin % should be:
However, if the AVG summary type is selected, NetSuite incorrectly averages the pre-calculated per-transaction Gross Margin % values:
This is also completely wrong.
The problem: NetSuite does not allow the percentage formula to be applied to the total revenue and total cost—only to pre-calculated per-transaction percentages, forcing users to select an incorrect summary type.
The Correct Approach: Use NetSuite Analytics Workbooks with Pivot Tables
Since Saved Searches force an incorrect summary type on percentage calculations, they cannot compute percentage-based metrics correctly at an aggregated level. Instead, the correct way to calculate percentages on summarized data is by using NetSuite Analytics Workbooks with Pivot Tables.
Why Workbooks Provide the Correct Calculation
- Workbooks First Aggregate Data Before Applying Calculations
- Unlike saved searches, where NetSuite applies the formula before aggregation, Pivot Tables within Analytics Workbooks allow the system to first summarize total revenue and total cost, then apply the Gross Margin % formula to the aggregated data.
- No Need to Assign an Incorrect Summary Type
- Since Analytics Workbook allows users to create a custom measure that applies the correct formula to the summarized data, you avoid the issue of SUM or AVG distorting the results.
How to Correctly Calculate Gross Margin % Using Analytics Pivot Tables
To ensure NetSuite calculates Gross Margin % correctly on summarized data, follow these steps:
Step 1: Create a New Workbook
- Navigate to Analytics > Workbooks and create a new workbook using the Transaction dataset.
- Set up filters to only include Sales Orders within the desired date range and location.
Step 2: Build a Pivot Table
- In the Pivot Table view, add the following fields:
- Rows: Group by Month
- Values:
- Revenue (SUM of Amount)
- Cost (SUM of Cost Amount)
Step 3: Create a Custom Measure for Gross Margin %
- Instead of using a standard formula field, create a Calculated Measure for Gross Margin % using the following formula:
- This formula ensures that NetSuite first sums all revenue and costs before applying the Gross Margin % calculation, producing the correct value.
Step 4: Add the Gross Margin % Measure to the Pivot Table
- In the Measures section, add the newly created Gross Margin % custom measure to ensure the percentage is correctly applied to the summarized totals, rather than being calculated at the transaction level before aggregation.
By leveraging NetSuite Analytics Workbooks and Pivot Tables, you can avoid the inaccuracies of summary saved searches and ensure your percentage calculations are always correct at the aggregated level.
Want to Learn More About NetSuite Analytics Workbooks?
If you’re new to Analytics Workbooks or want to explore more advanced techniques, check out some of our other posts:
NetSuite Analytics Part 1: Getting Started – An Overview of Benefits, Features, and Essential Techniques
Summary: This guide introduces NetSuite Analytics Workbooks, covering the core benefits, essential features, and step-by-step techniques to get started. Learn how to build a workbook, apply filters, and structure your data for meaningful insights.
Part 2: Intermediate Techniques in NetSuite Analytics – Leveraging Calculated Fields, Custom Metrics, and Formulas
Summary: Ready to take your analytics skills further? This guide dives into intermediate NetSuite Analytics techniques, including calculated fields, custom formulas, and advanced metrics. Learn how to fine-tune your data analysis and create dynamic, customized reports that go beyond standard saved searches.
Key Takeaways
- Saved Searches cannot correctly calculate percentages at the summary level because they require a summary type that distorts the result.
- Applying SUM to percentage formulas incorrectly adds all transaction-level percentages together.
- Applying AVG to percentage formulas incorrectly averages all transaction-level percentages, leading to misleading results.
- NetSuite Analytics Workbooks pivot tables allow percentages to be applied to the summarized totals, producing correct results.
For accurate reporting, always use NetSuite Analytics Workbooks when working with summary-level percentage calculations— your financial metrics will be far more reliable.
About Us
We are a NetSuite Solutions Partner and reseller with 30+ years of combined experience. We specialize in implementation, optimization, integration, rapid project recovery and rescue as well as custom development to meet any business need. If you would like more information on NetSuite or require consultation for your project or implementation, feel free to contact NetSuite support.
To Contact Us Click Here