Calculate Total Gross Profit Percentage Based on Total Revenue per Item in a Saved Search

Scenario

User needs to display in its custom saved search the total gross profit percentage per item based on the item’s total revenue.

Solution

1. Navigate to List > Search > Saved Searches > New
2. Click Transaction
3. Search Title: Enter Title preferred
4. Click Criteria
5. Click Standard
6. Filter:
• Select Posting
• Select Yes
• Click Set
• Click Add
7. Click Summary
8. Summary Type: Select Average
• Field: Select Formula (Percent)
• Formula: Enter Round(sum(case when {accounttype} in ('Income','Cost of Goods Sold') then nvl({grossamount},0) else 0 end)/nullif(sum(case when {accounttype} = 'Income'  then nvl({grossamount},0) else 0 end),0),4)
• Description: Select Is not empty
9. Click Results
10. Click Columns
11. Field:
• Select Item
• Summary Type: Select Group
• Select Formula (Percent)
• Summary Type: Select Average
• Formula: Enter Round(sum(case when {accounttype} in ('Income','Cost of Goods Sold') then nvl({grossamount},0) else 0 end)/nullif(sum(case when {accounttype} = 'Income'  then nvl({grossamount},0) else 0 end),0),4)
12. Click Save & Run