I had the honour of speaking at the Salesforce World Tour this year with the amazing Stefanie Bialas. She showed us the power of “The Power of One”, this is her blog on really how powerful it is!

Superpower Salesforce with the Power of One

Yeaahh, you’ve managed to build some excellent reports monitoring the pipeline of your company or also enabling marketing to contact all your prospects in the DB. And suddenly someone comes along and asks “How many opportunities do we actually have in the pipeline? The record count seems way to high” or “That’s awesome, now I can contact all our prospects. How many companies do we actually contacts?”. So how to address this in order to answer those questions?

You’ve probably noticed by then that the record count for reports like Opportunities with Products or Contacts and Accounts count the total number of child records associated with the parent record. So for opportunities, this is, for example, the sum of each product line item. Or a number of contacts associated with each account. How can you do this now to have a record count of the parent record, counting every record only ones? The answer is, use the Power of One!

Simply described the Power of One is a custom formula which you best create on each object and holds simply the value 1. When you sum it up in a report, 1 will be the result for each line item.
Here now a step by step guide helping you to setup the Power of One. I am taking the account and contact example to demonstrate this here but this works in the same way for all the other objects.
Go to your setup menu and select the object you’d like to count each record separately of. Create a new field:

For Step 2, fill in a name for your newly created field in “Field Label”. With tab, a field name will be created automatically. Number is what you should select as formula return type with zero decimals.

With next you will go to step 3. Now you get to write your formula which will be used to calculate the value you’d like to have displayed in your newly created field. Think again of what the purpose of this field is, we simply want to count the parent record only ones within our reports. And therefore all you need to insert into the formula canvas is 1 as a number:

That was easy, wasn’t it? Now save your newly created field and test it out. For the test go to your report tab and select for our example here “Contacts and Accounts” as a report type.  As usual first select the records you want to look at, then group the account field. Now your report will look like this:

As you can see the record count next to each account name counts the number of contacts associated with each account. That’s great to tell how many contacts we will have and can contact, but we won’t be able to answer the question of our Marketing Director how many companies we are contacting this way. Now your newly created superpower formula comes into place. Add it from the side panel with the quick find and drop it onto your report builder. Hoover over your field to open up the menu for the field and select “Summarize This Field”. You will get a window as shown below:

Check the checkbox “Sum” and click on apply. Now save and run your report to see the magic. When you scroll to the very bottom of your page you will now see two numbers. One being the classic record count as we know it, in this case counting each contact held in the report. And a second one which is the SUM of the Power of One for each individual account record. This number is logically lower (or the same) than the total count of the child records and gives you the total of accounts in your report. Yeeaahh!

Now share this with your manager and make yourself a well-deserved coffee.

Stefanie Bialas | ステファニ ビアラス
@schdaephyie
Marketing Operations Europe | NTT Europe

Slides from the World Tour London: