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!
Admin Hero 
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!
LikeLike
Thanks Chris! I haven’t actually taken this module yet so I’m excited to dig into it this week!
LikeLike
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?
LikeLike
The only downside I could use to using a checkbox is that you would need to convert True to equal a number in order to calculate it in a report, correct? Otherwise, I would think they work the same way.
LikeLike
It lets me do “sum” on checboxes and treats them like they are = 1, no extra step needed. (one of these ways is probably a more inefficient calculation on the back end, I could anticipate it making large reports run slower…)
LikeLike
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!
LikeLike
Hi Kim! I tried to find his session on Dreamforce as well, but wasn’t successful. He’s on vacation through the rest of today but I did ask if he would send me the name of the session when he is back. Once I know what it is, I’ll update the post!
LikeLike
This is great info. Thanks for sharing.
LikeLike
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).
LikeLike
Nice Article and thanks for the preview…kind of like flipping the classroom so we can have more discussion at your presentation!
LikeLike
Hi Brent:
I created this field on the Accounts object, now how do I use it on an Accounts Report?
Thanks
LikeLike
Pull this field into your report like you would any other field and you can sort and group on it, calculate sums and averages and more! Don’t forget that you may need to add this field to your custom report types. Find out how in this post: http://www.adminhero.com/the-hidden-functionality-of-custom-report-types/
LikeLike
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 this why Admins drink?
LikeLike
Hey Bruce! You are exactly right! This is where the Power of One really shines – we don’t really know what’s made up of that total for Record Count. The sum of the Power of One fields are higher than the total count and that is where the mystery continues! I’ll have to ping SteveMo to provide his colorful commentary!
LikeLike
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 and also I have read lots of answers that were given by SteveMo in Answers Board. You guys are the rock starts of Salesforce whom I want to be like one day. Thank you so much again and your work is great.
Mirzat
LikeLike
Thank you Mirzat! I appreciate that!
LikeLike
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?
LikeLike
Hi! I’m happy to help – where are you struggling? It is a bit of an abstract concept and to be honest, I struggled with how to use it for quite a long time. Are you trying to understand what the use cases are?
Once example I can provide is with a recent reporting request from a client. They wanted to get a count of records calculated at the parent. A traditional roll-up summary field would work here, except that the parent-child relationship was done via a Lookup, not Master-Detail. Since a Master-Detail is needed to use standard Salesforce rollup summary fields, we had to look for a different solution.
We ended up using the Power of One field on the child object to get our count, and an AppExchange tool (and Admin Hero sponsor) called Rollup Helper. Using Rollup Helper, we were able to create a rollup on the parent record showing the number of child records leveraging the Power of One field.
I hope that helps. If you’re still confused, let me know and I can provide more color.
LikeLike
@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 do anything like that, and you need to create a Custom Summary Formula within that Report that references the count of Users, Accounts, Opportunities, or Bottles of Beer on the Wall, the Power of One just might save your ass.
LikeLike
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”.
LikeLike
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 😦
LikeLike
I can tell you first hand that they have been extremely useful fields when I was an admin, and now as a consultant. I often create these fields on objects to meet reporting requirements in client’s orgs and it works like a charm! If you don’t have a use case, no need to create them.
LikeLike
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 of seeing SUM of Po1, Sum of Po1, Sum of Po1, Sum of Po1… I see Sum of Users, Sum of Accounts, Sum of Opportunities, Sum of Opportunity Products…
LikeLiked by 1 person
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!
LikeLike
Hi Neil! Yes, these fields will show up automatically on standard report types. You may need to add them to custom report types once they are created. Make sure that they are visible to at least the System Administrator. They don’t need to be on the page layout, but if you want users to be able to report on them, they need to have read access to the field.
LikeLike
Thanks Brent! That’s what I missed–I didn’t make it a visible field for anyone. It needs to be visible, but not in the page layout.
LikeLike
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.
LikeLike
Does this help in calculating the median?
LikeLike
I suppose it would depend on how you are wanting to calculate the median on. If you calculate the median of this field in particular, it would be “1” as this is the value of the formula field for all of the records in question.
LikeLike
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 other than what I would expect, so is this ‘mystery’ unknown number something I’m just not aware of. In what reports does the grand totals number not add up to what you would expect?
Thanks
LikeLike
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 would be to group by the ‘Has Products’ formula, or exclude these Opps all together (if appropriate).
I still think this is a useful tool for certain reports though, so I’ll definitely keep it in mind for the future. Thanks
LikeLike
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,
LikeLike
Yes, this can be done but the data has to be correct as well. I would have to work through an example in my dev org since it’s been a while since doing it, but I know it can be done!
LikeLike
This should be default behaviour for the bar chart. Let me know if that’s not the case
LikeLike
Hi Brett – great formula it has helped me out alot. I have a problem however at the minute with trying to find the average on a report when using the power of 1 field in a column. I can SUM the column however the average displays as “1”. Any way I can get the correct average showing?
LikeLike