Power of One: The Most Brilliant Analytics Trick of All Time

The Power of OneAnalytics 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:

Picture1

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:

Power of One Applied

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

Power of One Formula on Accounts

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:

Variance Report Chart

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!

Load More Related Articles
Load More By Brent Downey
Load More In Analytics

33
Leave a Reply

avatar
15 Comment threads
18 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
17 Comment authors
VishnuvardhanReddyChrisL01$Lan3NeilDave Recent comment authors
  Subscribe  
newest oldest most voted
Notify of
Chris Duarte
Guest
Chris Duarte

Hooray! I love this post, and love this formula. Thanks for linking to Trailhead. Because SteveMo weighed in on the Formulas & Validations module, we actually feature the Power of One in that module!

Aaron King
Guest
Aaron King

We have done similar, adding checkboxes to certain objects that are by default checked; They seem to be working as we are able to sum them in reports. Does anyone know pros and cons of Formula = 1 vs. the checkbox approach?

Kim
Guest
Kim

Can you please hyperlink to SteveMo’s session at Dreamforce, so we can register for the right one? I couldn’t find it when searching “power of one” in the Dreamforce sessions search. Thanks for the great post!

Dustin
Guest
Dustin

This is great info. Thanks for sharing.

Rachel Hands
Guest
Rachel Hands

So great! I use Power of One to get counts of how many unique accounts submitted cases in a given time frame (among other custom things).

Nate Bride
Guest

Nice Article and thanks for the preview…kind of like flipping the classroom so we can have more discussion at your presentation!

Carlos Siqueira
Guest
Carlos Siqueira

Hi Brent:

I created this field on the Accounts object, now how do I use it on an Accounts Report?

Thanks

Bruce
Guest
Bruce

I get that there’s a problem, and I get that the new field is needed, and have seen Steve Mo evangelize this elsewhere. But the example given in Brent’s post doesn’t make sense to me. (I know it shouldn’t)! The “Grand Total 1104 records” in the both screenshots does *not* equal the eventual total of Users, Accounts, Opportunities and Opportunity Products shown in the 2nd screenshot. It appears that 1104 is the sum of the various Record Owner #s above it (26+26+405+5+23+619), but those #s are not the sum of the User, Account, Opportunity, and Opportunity Products going across. Is… Read more »

Mirzat
Guest
Mirzat

Hi Brent, First of all, nice to ‘meet’ you finally, although this would not be considered as official, i would hope to meet you one day in person. Thank you so much for your great posts, I have, to be honest, benefited a lot from it and I believe there are a lot more people have also benefited from it. I am just going to make it short and express my appreciation. Thank you for your blogs which are unbelievably amazing to read and follow and super easy to understand. I learned a lot from you and Ben and David… Read more »

Nope
Guest
Nope

This really makes no sense. don’t understand the cow towing here. This is propogating creating a formula field with a 1 value, then does not go on to describe how it’s actually used in reporting. And the response to a comment on this to “pull it into reports” then report on it adds zero substance and is a total non-answer. Is everyone just pretending this actually works or does someone have an actual solution to the problem?

SteveMo
Guest

@Nope Do you ever need to create a Report that spans multiple Objects and also get the distinct count of records of each Object? Like maybe an Opportunities with Products Report where you need to display the number of Users who own an Opportunity within the Current Month or Quarter, and the number of Accounts those Opportunities are with, the number of Opportunities, the number or Opportunity Products? If you never need anything like that, then you don’t need to use the Power of One and you can be on your merry way… but if you ever do need to… Read more »

SteveMo
Guest

NOPE
Not for nuthin’ but did someone pee in your Cheerios or throw your hamster into a ceiling fan, or something?

Maybe you should take another look at the Report Screenshots that Brent posted, the ones that show “how it’s actually used in reporting” and how “this actually works”.

Dave
Guest
Dave

My boss keeps trying to get me to add these fields, but I think it’s a ruse to try to get me to add fields call POO all over the system 🙁

SteveMo
Guest
SteveMo

Dave you touched on a mistake I’ve seen a lot of people make over the years with The Power of One (Po1). They create Po1 fields on every object (like they should) but they name EVERY Po1 field on EVERY object “Power of One” or “Po1”. So then they create an Opportunity with Products and Revenue Schedules Report and there’s 4 fields all called “Power of One” so you can’t even tell what the Po1 is counting. Personally I name every Po1 Field after the Object it’s on (because that is what I want to count with it) So instead… Read more »

Neil
Guest
Neil

I created the PO1 fields on all the objects we report off of, and added them to some custom report types, but they’re not showing up in the field list on reports.
1. Do they show up on standard report types?
2. is there something else I need to do (I created the formula fields to be invisible everywhere, and read-only)
3. Why would they not show on custom report types if I’ve added them in the Edit Layout section?
Thanks!

SteveMo
Guest
SteveMo

Another thing I’ve heard is that in Professional Edition or lower the the field needs to be on the Page Layout in order to be selected in Reports. I’ve never used PE before so I dunno if it’s true.

L01$Lan3
Guest
L01$Lan3

Does this help in calculating the median?

Chris
Guest
Chris

First of all. thanks Brent and Steve for this insight. It’s honestly not something I have ever needed as a solution to a reporting requirement, but I can see it’s merits. One thing that’s confusing me is not being able to tell what the ‘Grand Total 1,104’ is referring to. Firstly, what report type is this screenshot from? I would have assumed this was an Opportunity Product report and the number would have been that, but the figures don’t correspond with the ‘Opportunity Product’ P01 field. I have honestly never noticed the Grand Totals figure on a report being anything… Read more »

Chris
Guest
Chris

I just ran a couple of test reports, and I think I answered my own questions. I’m guessing this is an ‘Opportunities with Products’ report, and that grand totals number represents the total number of Opportunity Products + the total number of Opportunities without any Products. E.g. if you had 3 Opps with 2 products each, and 2 Opps with no products the total would be 8 (3*2 = 6, 6+2 = 8) I guess I generally don’t generally report on Opportunities without Products, hence why I’ve never noticed this. A simpler approach to the issue of the incorrect figure… Read more »

VishnuvardhanReddy
Guest
VishnuvardhanReddy

Brett- is it possible to show as above you shown in the chart in the same way how can show Negative Revenue amount as down bars as minus values. can you please help me on this

Thanks,

SUBSCRIBE ALREADY!

SUBSCRIBE ALREADY!

Join the bomb diggity Admin Hero email list and never miss a post. Like, never ever!

Hizza! You're subscribed! Nothing but good times ahead!