…….dont wear heels!!! OK, that’s not entirely true, my biggest take away from London’s calling was the ‘Power of One’ formula and I’ll come onto that shortly, but I do feel compelled to mention that you definitely shouldn’t wear heels to this event, as I did. It’s flat shoes, trainers, all the way….

Right, let’s talk about the ‘Power of One’ formula. You are going to love this…

SteveMo (we all know him right?). You certainly will do if you are an active member of the answers community. Most of us owe him a beer (or two). I went to his session on “Formulas for the everyday admin” and I can honestly say, I walked away knowing the greatest formula ever written.

Now, before I continue, I must credit the originator of the formula, in Steve’s own words “the DemiGod of Analytics” Tom Tobin.  Steve also deserves credit too for sharing the knowledge (and the below screen shots), thanks Steve 

I must also credit  Brent Downey at Admin Hero for the below use case.

If, like me, you have no idea what this formula is, how it will help or why you should even care, keep reading…

The Problem

Salesforce reports, especially now we have the Lightning UI, are great for getting some decent metrics out of Salesforce. But there is a regular problem that you may not even know exists. That is the accurate but misleading count of records at the bottom of every report. Here’s an example:

The report’s total shows 1,076 records are a part of this Opportunity report. But what exactly is this count referring to?  Viewers of the report would assume that this number is referring to the count of Opportunities. But looking at the count of Closed records, it doesn’t seem to add up. So, what is making up the total?

Before the ‘Power of One’, there would be no good way to find out what the total represents except to go line by line through the 1,076 records…dull dull dull. But when we apply the ‘Power of One’ to the report, this is the result:

The Power of One allows us to tally each record. What this tells us is that the total of 1,076 records is split up between Users, Accounts, Opportunities and Opportunity Products. Not in a million years would you have figured that out without the Power of One.

Let’s see how this works.

The Solution

Formulas are complex little…things. You’ve got to know how to leverage functions and operators, and it can be a little like speaking a foreign language. The ‘Power of One’ is a formula, and relatively complex one at that. It’s a formula that honestly trips people up a lot (trust me, I saw the confusion on everyone’s faces in the room when SteveMo walked us through how to create this formula).

Here it is. Ready?

On each object, you’ll create a formula field where a field type is a number with zero decimals. The formula entered into the formula field is the number one (1). That’s it! Here are step-by-step instruction for setting this up on the Account object.

1. Click Setup | Customize | Accounts | Fields | New to create a new field
2. Choose Formula as the field data type and click Next
3. In the Field Label field, enter Accounts
4. For the Formula Return Type, select Number. Be sure to select ‘0’ for the number of decimal places then click Next
5. In the Formula Options section, where you create the formula, enter the number 1 then click Save at the bottom of the page
6. Repeat these steps for every object in Salesforce

That’s it! You’ve now created your ‘Power of One’ field on the Account object! There is no need to add this to the page layout or do any additional manipulation of the object itself.

One side note is that you may need to add this field to your custom report types as newly created fields are not automatically added to existing custom report types.

With this formula field, report formulas become more beneficial, allowing you to calculate things like Close Rate, Win Rate and Average Pipeline. You can even do variance reports that look amazing on a dashboard! Here’s one more screenshot from SteveMo showing a variance report:

Impressed? I was too! I’m sure you can see why this was my biggest takeaway from London’s calling.

Formula’s are, without a doubt, one of the most powerful features of Salesforce. If they aren’t your thing, check out the Formulas and Validations trail on Trailhead.