ANS-0263 · FINANCIAL REPORTING
How to Create a Custom KPI for Gross Margin Percentage in NetSuite
Learn to configure a NetSuite custom Key Performance Indicator to accurately track Gross Margin % using transaction saved searches.
Short answer
To create a Gross Margin % KPI in NetSuite, define a transaction saved search filtering for Income and Cost of Goods Sold account types. Use a formula to calculate ((Income - COGS) / Income) or sum amounts with specific summary types. Then, add this saved search as a custom KPI to your dashboard for real-time performance monitoring.
Scenario
Users need to monitor Gross Margin Percentage, calculated as (Sales - Cost of Goods Sold) / Sales, directly on their NetSuite dashboard. The goal is to create a custom Key Performance Indicator (KPI) that accurately reflects this financial metric for various periods.
Solution
To create a custom KPI for Gross Margin Percentage, two primary methods involving transaction saved searches can be employed.Method 1: Using Summary Types in Saved Search Results
Navigate to Lists > Search > Saved Searches > New.
Select Transaction from the List.
On the Criteria subtab of the Saved Transaction Search page, set the following filters:Account Type is any of Income, Cost of Goods SoldPosting True
On the Results subtab of the Saved Transaction Search page, configure the following columns. While these settings are valid for a detailed saved search, for a custom KPI designed to display a single aggregated value (like Gross Margin %), a formula-based approach (as demonstrated in Method 2) is generally more efficient for direct display of the aggregated percentage.DatePeriod (set the Summary Type to Group)TypeNumberNameAccountMemoAmount (set the Summary Type to Sum)
On the Available Filters subtab of the Saved Transaction Search page, set the Date field to be shown in footer.
Set a preferred search title.
Click Save & Run.
The resulting amount should align with the Gross Profit line on an Income Statement report for a given period.To add this saved search as a custom KPI:
Go to your Home Dashboard.
Click on Set Up under the Key Performance Indicators portlet.
Click on the Add Custom KPIs button.
Type in the custom saved search title on the search box.
Add the saved search and click Done.
Click Save.Method 2: Using a Formula (Recommended for Single Aggregated KPI Values)This method directly calculates the Gross Margin percentage within the saved search, making it ideal for displaying a single aggregated value in a custom KPI.
Create a Transaction Saved Search:1.
Go to Lists > Search > Saved Searches > New.1.
Scroll down and click 'Transaction' link.1.
On Criteria tab > Standard sub tab, set the following:a. Account Type is any of Income, Cost of Goods Soldb. Posting is true1.
On Results tab > Columns sub tab, set the following:On Field column: Formula (Numeric)On Summary Type SumOn Formula column:
(CASE WHEN {accounttype}='Income' THEN {amount} ELSE 0 END)-(CASE WHEN {accounttype}='Cost of Goods Sold' THEN {amount} ELSE 0 END)1.
On Available Filters tab, add the Filter:DateMark 'Show in Footer' check box.1.
Mark Available as Dashboard View check box on the main part of the Search form.1.
Edit Search Title as desired.1.
Click Save & Run.
On Home > Set Preferences > Reporting/Search tab > Reporting section > set the 'Report by Period' drop down field to 'Never' > click 'Save' button.
Add the recently created search to KPI portlet as usual.
To compare the 'Gross Profit' amount from standard 'Profit and Loss' Report vs. the recently created Search, run both in different tabs and change the 'Date' filter footer for each as desired.
Expert NetSuite Support
Need help with this NetSuite issue?
Financial Reporting consulting and configuration support