Ask Search:
Steve MolisSteve Molis 

Tips & Tricks: "The Power of One" the Greatest Formula Ever Written*

First bow and give thinks to the DemiGod of Analytics Tom Tobin (https://success.salesforce.com/_ui/core/chatter/groups/GroupProfilePage?g=0F9300000001pFV) (the guy who taught me "The Power of One").  Then create a new custom field on the object that you want to count in your Reports (my advice: do it on every Object in your Salesforce org). 

Documentation Create Custom Fields​

Step 1.  Choose the Field Type 
Select: Formula 
User-added image 
Step 2. Choose the Output Type 
Select: Number (0 decimals)
User-added image

Step 3. Build the Formula
1
that's it, just a number one, nothing else...  What??? why are you looking at me like that???  

The moment you click "Save" every existing record of that Object will have a number 1 on it, no matter how many thousands, millions, bahzillions of them you have on your SFDC org, and every record that is created after that for as long as you have your SFDC org and the stars shine and the Sun burns bright.  

So what's the big f*ing deal???  It's just a 1...  you create a Power of One field on an Account, you look at an Account and there's just a 1, what's so friggin' awesome about that???  

Grab a Report and add your shiny new "Power of One" fields to it and select Summarize(SUM) 
User-added image

Then Run the Report...

*** Spoiler Alert: Here's where the magic happens ***

User-added image
Now you can get the number of Opportunity Owners, the number of Accounts, the number of Opportunities, the number of Opportunity Products, Lightbulbs, Widgets, Bottles of Beer on the Wall... all using The Power of One    

But don't take my word for it...
User-added image
 
Mayank SrivastavaMayank Srivastava
Great stuff as always, SteveMo!
Steve MolisSteve Molis
PS.
Credit where credit is due, I learned "The Power of One" from Tom Tobin at Dreamforce'09, I'm just an Apostle spreading the good word... 

User-added image
Megan CollinsMegan Collins
I am literally laughing in my cubicle at this image! And, will try this out beginning of next week! 
Vinay ChaturvediVinay Chaturvedi
Great Post Steve.
You are awesome like always.
Lets mark a best answer to close this thread :P
Venkata BottaVenkata Botta
Many thanks
Shahid HusainShahid Husain
Excellent post Steve.
Abhishek SinghAbhishek Singh
Great Share Steve.
Pragadeesh R KPragadeesh R K
This is awesome!
Jonny KatzJonny Katz
great report. how can i make sure that if opp is lost the amount does not include that opp value?
Jane BradyJane Brady
Oh Wow. Love it!
Steve MolisSteve Molis
@JohnnyKatz 
You could create a Formula(Currency) field like this:
 
IF(
	OR(
	IsWon = TRUE,
	IsClosed = False),
Amount,
NULL)

 
Venkata KarthikVenkata Karthik
Hi Steve, 

Can you look it on the 'Contact' Object : https://success.salesforce.com/answers?id=906300000019MkN

Thanks
 
Steve MolisSteve Molis
Venkata, I've posted a reply in your link
Steve MolisSteve Molis
User-added image
Barbara CliftonBarbara Clifton
I needed this Power of One last month! SF tech support didn't know this... I had to count lines in a spreadsheet!  Thanks so much for saving my time, my eyeballs... and keeping me lookin' like a SF RockStar!! 
 
Steve MolisSteve Molis
Thanks BarbraC, 
Tom Tobin created "The Power of One" and taught it to me, I'm just an Apostle spreading his word.
Steven DoeblerSteven Doebler
User-added image

I'm doing something wrong because i don't have the option to add that custom account info or summarize by the field. Ive added Power_of_1 custom field to all major objects. Thoughts?
Steve MolisSteve Molis
StevenD
Which Report Type are you using?  
Also, I twould not recommend calling your Power of One fields "Power of One" (although that is pretty Meta) it gonna make it hard to keepm track of what is being counted in the Report Results.
 
Personally I name mine after the Object that I am counting (Accounts, Contacts, Opportunties, Users, etc) but you can name them Moe, Larry, Curly, and Shemp if you want. 
 
Steve MolisSteve Molis
PS.  You don't "Summarize the Report" by the Power of One field, all that will give you is a 1.  You group the Report by another field like Opportunity Owner, Stage, or something else, and select the Power of One field in the Report Details and select Summarize(SUM) 
Steven DoeblerSteven Doebler
This is just a opportunities report. I did call the fields Power_of_1_product, account, lead etc. I guess i dont have the option to add Account Custom info: # Account is my problem to even summarize by one of those fields. 
Steve MolisSteve Molis
No, an Opportunties Report Type will give you access to custom fields on the User, Account, and Opportunity object.  Did you give uesrs "Read" access to the Power of One fields when you created them? (you can't add what you can't "see")
Steve MolisSteve Molis
User-added image
Steven DoeblerSteven Doebler
I have the default on of visible and read only but the field accessibility is hidden because i removed it from the page
Steve MolisSteve Molis
 Are the Account Classification and Distribution Account fields on the Page Layout?  
Are you using Professional Edition or Lower?  If you are you'll need to add the Power of One fields to the Page Layout
Steven DoeblerSteven Doebler
Account Classification is not, distribuition is. Enterprise Edition. 
Steve MolisSteve Molis
Can you click on the Field Accessability and Field Level Security settings for your Account:Power of One field and post screenshot of those?

In SFDC there are basically 3 reasons why something doesn't show up in a Report: 

1. It does not meet the scope or filter criteria of the Report
2. The User does not have at least Read access to it
3. It does not exist 
Steve MolisSteve Molis
Just to reiterate this is a "Plain Vanilla"  straight outta Salesforce: Opportunity Report, I didn't pull it out of the Keelber Hollow Tree, there's no Elfin' Magic going on in here.

User-added image




 
Steve MolisSteve Molis
I don't understand why you made the field "Hidden" (there's no reason for that), just make it Read Only and don't add it to the Page Layouts
Tiffani ByrdTiffani Byrd
This is awesome and thanks for sharing :D adding this to my tool kit!
Steve MolisSteve Molis
StevenD 
Are you sure you're not using a report built from a Custom Report Type?
Steven DoeblerSteven Doebler
Just hit new report and selected Opportunities
Steve MolisSteve Molis
Did you make the Account Power of one field fully accessible to all users?  Or do you still have it hidden?
Steven DoeblerSteven Doebler
Should be visible User-added image
Steve MolisSteve Molis
Wait, the field is still called "Power of 1"?  I thought you had renamed it "Account"  (that's what you're searching for in the screenshot you posted) 
Have you tried searching on "Power"?
Steven DoeblerSteven Doebler
Thats the field lable, API has account. User-added image
Steve MolisSteve Molis
Why did you just change the API Field Name and not the Field Label?

In the Report Builder Field Search you need to use the Field Label, not the API Field Name, and the Field Label is what's gonna show up in the Report results.   

Like I said before: I would not recommend calling your Power of One fields "Power of One", that's gonna make it pretty hard to keep track of what is being counted in the Report Results.  
Steven DoeblerSteven Doebler
I added the object name to the field lable but as in the original i had already added Power of 1 account to the report. Field lables are not Power of 1 Account etc. 
Steve MolisSteve Molis
Whut the...  who the... whut???  I don't even know what we're talking about anymore.  Are you able to find the Account Power of one Field in your Opportunity Report or not?  

Can you post a complete screenshot that shows all of the Account Object Custom (Number) Fields that are available in the Report Builder (like this)

User-added image
Steven DoeblerSteven Doebler
Yea sorry i feel like i am just missing something obvious. User-added image
Steve MolisSteve Molis
Yeah, I think you are... 
User-added image
Steven DoeblerSteven Doebler
Do I add that field and then summerize by that field and do sum?
Steve MolisSteve Molis
Ummmm....  yeah, that is what I have been saying to do this whole time and in these screenshots that I have posted

Did you look at any of these when I posted them or read through the instructions I posted in the original thread?
User-added image
Steven DoeblerSteven Doebler
Yes i got it thanks, sorry for the confusion
Steve MolisSteve Molis
No problem, I'll put it on your tab 

#YouOweMeaBeer
Karthik PamarajuKarthik Pamaraju
Thanks a lot for this post @SteveMo. I was able to help others with this idea. #IOweYouaBeer
Barbara HanvilleBarbara Hanville
Steve -
This seems to be the solution for me, but I can't figure out how you are summarizing so that the individual line items are no longer showing up.  I'm sure this is a basic question, but can't find the solution (likely searching on the wrong thing).

Thanks in advance!  Barbara
 
Steve MolisSteve Molis
Hi Barbara,

Can you elaborate on what you're trying to do?  The "Power of One" formula will not filter Report Results, it simply allows you to get the distinct count of each type/object of record in a Report that spans multiple Objects.   
Barbara HanvilleBarbara Hanville
Steve,

I am trying to create a report that lists each account with a few 'account' fields, with a count of the number of contacts for the account and the number of opportunities for the account.  Ex:

ABC Company    State    Account Owner   # Contacts    # Opportunities

It could be that I'm not starting out with the right report type.  I have the custom fields set up for Contacts and Opportunities.  Appreciate it if you could point me in the right direction! 

Thank you!  Barbara
Steve MolisSteve Molis
So you want the Account detail information, and just the summary of the Contact and Opportunity data?  

Can you post a screenshot of the Report (including the Report Type being used) and the Groupings, Filters, and Settings, etc. that you're currently using and create a mock-up of what you want your Report to look like?  

​For questions related to Analytics: Reports and Dashboards those are really helpful 
Barbara HanvilleBarbara Hanville
I can't locate a report type that allows access to all three (Accounts/Contacts/Opportunities) and their custom fields.  In lieu of that I'm practicing with just Accounts / Contacts to get this to work.  I'll pull the details of what I have so far together and send it over.  If you can tell me what report type I should start with to have access to all three and their custom fields, I can start there instead.

Thank you!!
Steve MolisSteve Molis
Okay if you don't need the Opportunity details like (Close Date, Stage, Opportunity Name, Owner, etc)  you could create a Roll-Up Summary field on the Account object that returns the Count or $Amount of the Opportunties within the Account (here's more info)

Documentation Roll-Up Summary Field
https://help.salesforce.com/apex/HTViewHelpDoc?id=fields_about_roll_up_summary_fields.htm&language=en_US

Then you can use a plain vanilla Contacts and Accounts Report to get the Contact and Account details with the count of Opportunties. 

If you need to see the details of all 3 you're gonna need a Joined Report that uses a Contacts and Accounts Report for one Report Block and an Opportunity Report for the other Report Block 

Documentation Combine Different Types of Information in a Joined Report
https://help.salesforce.com/apex/HTViewHelpDoc?id=reports_working_with_joined.htm&language=en_US
Barbara HanvilleBarbara Hanville
I'm currently using the report type 'Contacts & Accounts' under Accounts and Contacts.  Here is a screen shot of where I am so far:
User-added image
The count is showing up, but I'm getting repeated lines for the Account details that I would like to show only once (preferably on the same line that I have the Account Name and Count of Contacts showing.  I assume it's I'm not using the correct report type.

Thanks!  Barbara
Steve MolisSteve Molis
Yeah, that's what I meants when I said "The "Power of One" formula will not filter Report Results, it simply allows you to get the distinct count of each type/object of record in a Report that spans multiple Objects.   "
Barbara HanvilleBarbara Hanville
Thank you for all the links.  All new techniques for me, so will dive in and see how far I get!  Appreciate the assistance Steve!
Anthony TurleyAnthony Turley
ROFL. I've been using this for a while but I never knew the origins. I have been enlightened.
pratima shrivastavpratima shrivastav
Thank you for the wonderful post... @Steve
Benjamin DolarBenjamin Dolar
@Steve Molis: From everything I have been working on and reading, if you use the Power of One on the User object, it seems to only apply towards record ownership, and not any other facet (like created by, or last modified by).  The I-owe-you-a-beer question is this: Is there a way to leverage this same idea against the creator of a record?

Here's the scenario: A team of individuals (call them the A-Team) manages leads.  When they convert a lead, then also create an opportunity, but during the conversion, then assign the converted account, contact, and oppty to another user (a member of the B-Team) in the system to manage. They also occasionally create Opportunities on existing Account records if they find that the lead already exists in the system as an Account/Contact.  

I want to see all created opportunites, grouped by created month, grouped by the creator, and be able to get a count on how many creators there were for that month as well. If I try to use the PO1 field on the user record, the report will tally the unique number of Opportunitiy Owners, which is irrelevant for this requirement.  The end goal is to figure out, on average, how many opportunities were created per A-Team member, based on the count of opptys and the count of creators, for any given month.
Steve MolisSteve Molis
Hi Ben,

You'd probably need to create a Custom Report Type (CRT) and use the "Add Related Fields via Lookup" option to reference the User Object (Po1) fields via the Created By and Last Modified By fields on the Account, Lead, Opportunity, Case, or "Beers owed to SteveMo and Tom Tobin".  
User-added image

 
Benjamin DolarBenjamin Dolar
That was the missing piece!  I had forgotten about the related fields via lookup option for CRTs.  I just added the UserCount field (my PO1 field on the User object) to a CRT for Accounts & Opportunities and was then able to summarize the correct number of Creators.

SteveMo, +1 Beer.  BenDo, -1 Beer.
Steve MolisSteve Molis
Three things:

1.  You forgot Tom Tobin
2. You owe us WAY more than 1 beer
3. I drink the good shit
User-added image
Amy CoplenAmy Coplen
This is amazing! How did I not think of this? lol Thank you so much!! 
Adi KamdiAdi Kamdi
Thanks a lot I had spent a couple of days on a report till I finally stumbled upon this article. Though I should mention it could be tricky adding power of one or any ccustom field on a custom report but that is a diffrent issue altogether 
Steve MolisSteve Molis
Hi Adi,
You just need to remember to update any Custom Report Type layouts after you create the Power of One fields, after a while it becomes second nature.
Stacey SaklikarStacey Saklikar
Hi Steve, is there a way to do this against a lead, contact, campaign member??
Steve MolisSteve Molis
Stacey Saklikar

Sure!  What are you trying to do in the Report?  Can you give me an example?  
Stacey SaklikarStacey Saklikar
For each record – in this example a lead John Smith, a lead gen rep has created 10 tasks associated with John Smith. I would like to add a field on the lead record that will sum the amount of activities and be able to report on that (# of activities = 10) I want to be able to do this for a lead, contact, account and opp. I was told the power of one field was the way to proceed. Thank you, Stacey Saklikar Salesforce Certified Administrator 408-858-1831
Steve MolisSteve Molis
Stacey Saklikar
Okay, for that you need Apex Code, or Declarative Roll-Up by Andy Fawcett (it's free) 
https://andyinthecloud.com/2016/06/19/declarative-rollup-tool-summer-release/

The Power of One is just for Reports.  If you had a Report and you wanted to see the count of Activities, the Count of Accounts, Contacts, Cases, Opportunties, etc, then you'd use The Power of One. 
 
Stacey SaklikarStacey Saklikar
Thanks, found out we want it in reports too. Best way to create? Thank you, Stacey Saklikar Salesforce Certified Administrator 408-858-1831
Steve MolisSteve Molis
Just follow the steps that are posted at the top of this thread. Create Power of One fields on those objects Add the Power of One fields to the Reports Select Summarize(Sum) Wash, rinse, repeat...
Emma FisherEmma Fisher
This thread has been really useful thank you to all..  however im still left wondering whether using the power of 1 you can then  use it toit to summarise other fields ? 
Using an opportunities with product report filtered to opp that have a certain product, there can often be more than on of this product type agaist the same opportunity - I have used the power of one field to give me the number of unique opportunities, but the next part im finding difficult, I want to total up the 'Amount' Field for just the unique records so that I can display a total value again opportunities with this specific product. Usually I would export and do it in excel however I am looking to set something up in SF for other users. Any ideas would be great 
 
Steve MolisSteve Molis
Hi Emma,

Did you also add the Power of One (Po1) fields to the Opportunity Product and Product objects?   

Can you post a screenshot of the Report, and the Groupings, Settings, etc, that you're currently using and the results you're getting and create a mock-up of what you want your Report to look like? For questions related to Analytics: Reports and Dashboards those are really helpful
Erik van den HurkErik van den Hurk
Hi Steve, I have the feeling I am missing something very simple here..
I have created a report on Accounts with/without activities. Now I'd like to see the percentage of accounts with activities in relation to the total portfolio of the sales rep. As you can see, the report now gives me 2/12 while the 2 activities were on 1 account. I would need 1/12.
 Screenshot of report

I have the nagging feeling it is incredibly basic but I can't wrap my head around it..

On the plus side.. Dutch beers are kinda famous, right?
Steve MolisSteve Molis
Can you post a screenshot of your Custom Summary Formula an Settings?
Steve MolisSteve Molis
Here you go

Just use your Activity Power of 1 filed as a Report Group or Bucket 
Then add a Report Custom Summary Formula like this
User-added image

User-added image
Erik van den HurkErik van den Hurk
Hi Steve,
I added your formula, but that still doesn't give me a challenge:
The report cosists of a list of sales reps. When I drill down to one Sales rep, the correct % shows somewhere in the matrix 1/12 = 8,33
User-added image
However, when in list view of all reps, the % goes wrong, as it starts dividing by the total number of accounts nationwide in stead of owned by rep :
User-added image

The formula I had in my old formula field was: 
User-added image
But that adds the number of activity lines, instead of the number of accounts they are on..


For all your help, do make sure to leave a delivery address..
User-added image
 
Erik van den HurkErik van den Hurk
@steve please don't tell me the green bottle scared you off? We have other brands.. And I could really use your view on the report. I am stuck as a bottle cap..
Edward ScottEdward Scott
Is there anyway to do this on a Tasks and events report type. So the Dales Associates at out company send out emails and log calls to leads and contacts everyday. I am trying to get a count of each unique contact or lead that is on the list.

Thanks,
Ed
Steve MolisSteve Molis
Hi Ed, you could do that using The Power of One and either an Actvities with Contacts Report, or an Activities with Leads Report.
Steve MolisSteve Molis
@Erik van den Hurk 

Sorry, but I'm having a hard time trying to visualize what you're trying to do in the Report
Kris RyanKris Ryan
@Steve, is there a way to count by Ultimate Parent? It counts the child Accounts. Anyway around this? The glitch is trying to do this on a Campaign Member report.
Steve MolisSteve Molis
Hi Kris,
Can you post a screenshot of the Report, and the Groupings, Settings, etc, that you're currently using and the results you're getting and create a mock-up of what you want your Report to look like? 
Kris RyanKris Ryan
Hi Steve,
User-added image
User-added image
User-added image
User-added image
Want the count to show 1 for each Ultimate Parent grouping so there can be the correct total at the end in Grand Totals. It is totallilng the number of children accounts of the Ultimate Parent.
Steve MolisSteve Molis
Hi Kris,
Create 2 custom fields, both of them
Datatype = Formula
Result = Checkbox 

Field 1: Ultimate Parent
Formula:
ISBLANK( ParentId )

Field 2:Child Account
Formula:
NOT(ISBLANK( ParentId ))

User-added image
Kris RyanKris Ryan
Let me test that out.
Steve MolisSteve Molis
No problem, I'll leave your Bar tab open 
Kris RyanKris Ryan
Does not work if you do not have anything tied to that top level in the report.
User-added image
Steve MolisSteve Molis
Whut the...???  
Steve MolisSteve Molis
This is what I get using a plain vanilla Report Type: Campaigns with Contacts 

User-added image
Kris RyanKris Ryan
Try it with one that has an Ultimate Parent account but  the Parent Account does not have a contact on the Campaign. You will get 0, like mine. The Ultimate Parent account does not have any contact campaign members. Make sense?
Steve MolisSteve Molis
Okay, if that's what you're trying to do, it would be kinda nice to have that information up front.  

Lemme see what I can do, we may need to create a Cusrom Report Type or a Joined Report
Kris RyanKris Ryan
Sorry... didn't know until I tested it out.
Steve MolisSteve Molis
Okay the only Accounts that are goona show up in a standard Campaigns with Contacts report, are gonna be account that HAVE a Campaign Member, same goes for an Opportunity Report, or a Contacts Report, etc, etc.  The Ultimate Parent Account fields reside on the Parent Account record, so you're not gonna be able to access them from a report that does not have them in the results
Kris RyanKris Ryan
That's what I was thinking, too. Just wanted to be sure there was not something I was missing.
Thank you !!
Steve MolisSteve Molis
Okay, it looks like you're off to the Land of Custom Report Types and Joined Reports. 

#SorryBabyIhadtoCrashThatHonda 
https://youtu.be/5lL1ypndnWA?t=47s
Sunil ShahSunil Shah
Awesome explanantion of Power of One Steve Molis :) Your following video explanation is also awesome: https://www.salesforce.com/video/296533/
Steve MolisSteve Molis
Thanks Sunil!
Tracy SandersTracy Sanders
Hi Steve,
I'm thinking of using a Power of One field in Activities to assist me in my dilemma of entering Activity quotas by rep by month (they are different for each rep and each month).  I have created a new Activity record type to enter their quotas.  Can I create a Power of One field and make it work for only certain Activity record types?
Hayley TullerHayley Tuller
I just found this and it UTTERLY BLEW MY MIND!!!
Steve MolisSteve Molis
Hi Tracy, sorry I missed your post, I think you may need to creat a custom Quota field on the Activity object.  Otherwise you would need to create a separate Quota Activity record until you had the same number of records as the Goal/Quota 
Anna BloemenAnna Bloemen
When using the Power of 1, does this formula recognize that a record is unique based on the Record ID? 
Steve MolisSteve Molis
Hi Anna, the "Power of 1" gives you the distinct count of Records of that particular object.  So in a sense, "yes" each record has a unique Record.Id in Salesforce and will have it's own "1"  
Anna BloemenAnna Bloemen
Thanks Steve. I ask because I am looking for a solution that allows me to identify duplicate leads based on Name and Email address (similar to the Conditional Formatting functionality in Excel), as opposed to matching on the Record ID.

Not to discredit Power of 1! This is awesome and have implemented across my org!
Steve MolisSteve Molis
Okay the Power' of 1 will not de-duplicate records (it was never intended for that)  
For that you'd need to use the methods you already described or a dedcated De-Duplication Tool for the AppExchange