Scenario
Create a saved search to show Gross Profit by Customer.
Solution
1. Go to Lists > Search > Saved Searches > New
2. Select Transaction
3. Enter a search title
4. Under the Criteria tab > Standard subtab, add the below:
a. Account Type > any of > Other Income, Income, Cost of Goods Sold
b. Posting = True (select Yes)
c. Date > enter the date range of your preference
5. Under the Results tab > Columns subtab > click Remove All, add the following:
a. Customer Fields... > Name
--- Summary Type = Group
b. Type
c. Document Number
d. Formula (Numeric)
--- Summary Type = Sum
--- Function = Round to Hundredths
--- Formula = CASE WHEN {accounttype} = 'Cost of Goods Sold' THEN {amount} ELSE 0 END
--- Custom Label = Total Cost
e. Formula (Numeric)
--- Summary Type = Sum
--- Function = Round to Hundredths
--- Formula = CASE WHEN {accounttype} = 'Income' THEN {amount} ELSE 0 END
--- Custom Label = Total Income
f. Formula (Numeric)
--- Summary Type = Sum
--- Function = Round to Hundredths
--- Formula = CASE WHEN {accounttype} = 'Income' THEN {amount} ELSE 0 END - CASE WHEN {accounttype} = 'Cost of Goods Sold' THEN {amount} ELSE 0 END
--- Custom Label = Total Gross Profit
g. Formula (Percent)
--- Summary Type = Sum
--- Formula = sum(case when {accounttype} = 'Income' then {amount} else 0 end - case when {accounttype} = 'Cost of Goods Sold' then {amount} else 0 end) / NULLIF(sum(case when {accounttype} = 'Income' then {amount} end),0)
--- Custom Label = Gross Margin
6. Under the Results tab
--- Show Totals = True (check the checkbox)
--- Sort By > Customer : Name
7. Click Save & Run