Scenario
There is no specific report for Inventory Profitability that can be selected in Report Snapshot in dashboard.
Solution
As an alternate solution, a Saved Search can be created that can show the revenue, cost, and profit of Items. These search can be posted in KPI in dashboard.
A. Create a Transaction Saved Search:
1. Navigate to Reports > Saved Searches > All Saved Searches > New.
2. Click Transaction
3. Search Title: Enter a new Title
4. Click Criteria tab
5. Click Standard sub tab
6. Filter:
- Account Type = is any of Income, Cost of Goods Sold
- Posting = is True
- Item fields : Costing Method = is any of Average, FIFO, LIFO, Lot Numbered, Serialized, Standard
7. Click Results tab
8. Sort by = Item : Name
9. Click Columns sub tab
10. Filter:
- Date
- Type
- Item : Name | Summary Type = Group
- Amount (Gross)
- Formula (Currency) | Summary Type = Group | Formula = case when {accounttype} = 'Income' then ({grossamount}) else 0 end | Custom Label = Revenue | Summary Label = Revenue
- Formula (Currency) | Summary Type = Group | Formula = case when {accounttype} = 'Cost of Goods Sold' then ({grossamount}) else 0 end | Custom Label = Cost | Summary Label = Cost
- Formula (Currency) | Summary Type = Group | Formula = (case when {accounttype} = 'Income' then ({grossamount}) else 0 end) - (case when {accounttype} = 'Cost of Goods Sold' then ({grossamount}) else 0 end) | Custom Label = Gross Profit | Summary Label = Gross Profit
- Formula (Currency) | Summary Type = Group | Formula = round((sum(case when {accounttype} = 'Income' then ({grossamount}) else 0 end) - sum(case when {accounttype} = 'Cost of Goods Sold' then ({grossamount}) else 0 end))/nullif(sum(case when {accounttype} = 'Income' then ({grossamount}) else 0 end),0),4) | Custom Label = Gross Profit % | Summary Label = Gross Profit %
11. Click Available Filters tab
12. Filter:
Date | Show in Filter Region = T
13. Mark the Available as Dashboard View
14. Click Save & Run
Upon seeing the results, notice the Gross Profit % column total is just the sum of all the percentages wherein it should follow the formula using the total columns. Currently, this is tracked under Enhancement 163996
B. Add the created Saved Search on Step A in the KPI Dashboard
1. Click Set Up under the Key Performance Indicators portlet
2. Add Custom KPIs
3. Look for the name of the saved search created on Step A
4. Click Add
Note: Range and Compare Range can be changed.