Salesforce Hack: The Power of One
From beginners to veterans alike, all Salesforce Admins suffer from the same problem: inaccurate reports!
Say you have ten Accounts, each with five Opportunities. Your ‘Grand Total’ record count would say 50. But a report stating that you have fifty unique Accounts is misleading (and may land you in some hot water with management).
That’s where the Power of One comes in!
Here are the step-by-step instructions for leveraging the Power of One Salesforce hack for yourself.
Step 1: Choose the Desired Object in Setup
Decide which Salesforce object you’d like to tally.
For tutorial purposes, we will use the Account Object.
Note: You can use this hack on any Standard or Custom Object.
Click the Fields tab (pictured right) under the Account Object.
This will bring you to all of the Fields on the Object.
Step 2: Create a New Field
Scroll down to the Custom Fields & Relationships section and click New
Step 3: Set Field Type
Salesforce defines a Formula Field type as:
“a read-only field that derives its value from a formula expression you define.”
Power of One uses this field type as an increment operator to accurately tally up your Object total when you run the report.
Step 4: Select Output Type
Name your field in the Field Label text box. We’ll call it “Account (PO1)”
Then, select Number as the Formula Return Type.
Note: make sure the decimal places option below the data type screen is set to 0.
Step 5: Set Formula to '1'
In the advanced formula text box, simply enter 1.
Salesforce 101 says you should always put a description in your Custom Fields.
Note: Leave the help text blank (it should not appear on the page layout and only be used for reporting)
Simply hit next and you are all set!
Step 6: Create and Run the Report
Build the report, add the new field you created (“Account (PO1)”), and summarize that field.
Your “hacked” report will contain an accurate count of Opportunity records and Account records.
In this case, there are eight Opportunities at one Account and the Formula Field isn’t double counted based on the related Opportunity Records to that one Account.