Analytics is one of those areas of Salesforce that can scare some folks. It can be frustrating and even daunting to try and figure out how to create the types of reports and charts that stakeholders are looking for. I suppose that's why Analytic themed tips and tricks are so sought after!

If you have been on the Answers community, you have probably seen this analytics hack called the Power of One; created by Thomas Tobin of Salesforce, evangelized by Steve Molis (SteveMo). But if you are like me, you have no idea how this works, how it will help or why you should even care!

I reached out to SteveMo and asked if he would share with me some of this screenshots and use cases for the Power of One, and the below post is the out put of that quick conversation. Let me take a quick second a plug SteveMo's theatre session at Dreamforce on the very topic in the Admin Zone. I've promised not to steal this thunder with the surprise ending, so you'll probably want to be sure that you attend this session if you're going to Dreamforce this year!

The Problem

Salesforce reports are pretty good 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,104 records are a part of this closed 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 makes up the total in the report?

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 1104 records. But when we apply the Power of One to the report, this is the result we get:

The Power of One allows us to tally each record. What this tells us is that the total of 1,104 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 buggers. You've got to know how to leverage functions and operators, and it can be like speaking a foreign language. The Power of One is a formula, and relatively complex at that. It's a formula that honestly trips people up a lot (trust me, I've seen the confusion on the Answers boards when SteveMo posts instructions on 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! No need to add this to the page layout or do any additional manipulation of the object itself. The formula is now working! 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:

If you want to learn how to make this and other similar reports check out SeveMo's session in the Admin Zone this year. If formulas, in general, aren't' your thing, check out the Formulas and Validations trail on Trailhead and improve your proficiency! Formula's are, without a doubt, one of the most powerful features of Salesforce.

Again, I want to extend a very special thanks to Steve Molis for allowing me to use his screenshots for today's post! You, sir, are amazing, and I'm glad to call you a friend! For those of you who don't know, SteveMo just recently reached 3,000 best answers on the Answers community. If you have ever received an answer from him or leveraged something he's already used, please buy him a beer. He deserves it!

Have you used the Power of One in your org? Tell me about it by leaving a comment below! I'd love to hear your use case!