Tips & Tricks: "The Power of One" the Greatest Formula Ever Written*
Documentation Create Custom Fields
Step 1. Choose the Field Type
Step 2. Choose the Output Type
Select: Number (0 decimals)
Step 3. Build the Formula
1that'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)
Then Run the Report...
*** Spoiler Alert: Here's where the magic happens ***
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...
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...
You are awesome like always.
Lets mark a best answer to close this thread :P
You could create a Formula(Currency) field like this:
IF( OR( IsWon = TRUE, IsClosed = False), Amount, NULL)
Can you look it on the 'Contact' Object : https://success.salesforce.com/answers?id=906300000019MkN
Tom Tobin created "The Power of One" and taught it to me, I'm just an Apostle spreading his word.
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?
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.
Are you using Professional Edition or Lower? If you are you'll need to add the Power of One fields to the Page Layout
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
Are you sure you're not using a report built from a Custom Report Type?
Have you tried searching on "Power"?
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.
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)
Did you look at any of these when I posted them or read through the instructions I posted in the original thread?
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
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.
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
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
Documentation Roll-Up Summary Field
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
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.
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.
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".
SteveMo, +1 Beer. BenDo, -1 Beer.
1. You forgot Tom Tobin
2. You owe us WAY more than 1 beer
3. I drink the good shit
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.
Sure! What are you trying to do in the Report? Can you give me an example?
Okay, for that you need Apex Code, or Declarative Roll-Up by Andy Fawcett (it's free)
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.
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
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
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.
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?
Just use your Activity Power of 1 filed as a Report Group or Bucket
Then add a Report Custom Summary Formula like this
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
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 :
The formula I had in my old formula field was:
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..
Sorry, but I'm having a hard time trying to visualize what you're trying to do in the Report
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?
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.
Create 2 custom fields, both of them
Datatype = Formula
Result = Checkbox
Field 1: Ultimate Parent
ISBLANK( ParentId )
Field 2:Child Account
NOT(ISBLANK( ParentId ))
Lemme see what I can do, we may need to create a Cusrom Report Type or a Joined Report
Thank you !!
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?
Not to discredit Power of 1! This is awesome and have implemented across my org!
For that you'd need to use the methods you already described or a dedcated De-Duplication Tool for the AppExchange
Formula Fields like "The Power of One" auto-magically work on any existing record as well as any newly created records
What I was trying to do was to have a report that show the number of campaigns with leads. The current report I created actually gives me the number of "Leads in Campaign" not the number of "Campaigns with Leads"
I used Campaign with Leads report type.
I may have done it wrong. Here's the view of my report builder and what I set it up for the Number of One field.
Thank you so much!
It looks like you created a Datatype = Number field and set the default value to 1 (that won't work)
That is not what I posted (scroll all the way back up and look)
Step 1. Choose the Field Type
Step 2. Choose the Output Type
Select: Number (0 decimals)
Step 3. Build the Formula
So I went back and tested to update one campaign and now that field starts counting that campaign.
Just to re-explain what you said to make sure I get this simple idea is that this Power of 1 magic is not retroactively updating existing records unless I go back and massly update those records but going forward, it will update for any newly created records, right?
Sorry, but I'm a little confused now.
Did you start over and create a new Field on the Campaign Object and select?
Type = Formula
Result = Number
Formula = 1
Type = Formula
Result = Number
Formula = 1
Went back into the report to pull that field in. Nothing came up and still showed blank value.
I grabbed one campaign and made an update to that record and saved.
Went back to the report, now the Number of Campaign field is showing 1 value.
My guess is that I had to update existing records in order for them to be shown in the report like your original explanation.
The power of 1 field just don't magically update all existing records without you going back to manually update them.
Field type = Formula
Return type = Number
Decimal Places = 0
Formula = 1
I promise I followed exactly the 3 steps.
Datatype = Number
Default Value = 1
I would delete and erase that field asap so there is no confusion
p.s. what does "tiy" stand for? :)
I am using the power of 1 in this report but I can't work out how to total up the counts.
I am looking to see how many times a person has logged into SF - The total for the 1st person should be 5 rather than 1
That won't work because a Date is not a record unto itself that can be counted. To do what you're trying to do in that Report you would need to create a custom "Power of One" field on the Login Date Object (and that's just not possible in SFDC)
It has been wonderfully explained by you. As many times I saw it, I want to thank you again and again.
Thank you for the great article and the time you have spent answering follow-up questions.
I've read through all the previous posts and am still having trouble with my Power of One field. I have a report grouped by agency and I want each agency to only be counted 1 time, regardless of the number of relationships that exist for that agency. For agency A, B, and D there isn't a problem because these agencies only have 1 relationship. But agency C has 2 relationships. A colleague directed me to the Power of One solution, but it is still showing a count of 1 for each relationship. My apologies if I'm overlooking something obvious, but would appreciate any help you can provide. Thank you in advance.
Where is your "Power of One" field in your Report screenshot?
Which Report Type are you using? If it's a Custom Report Type did you update the Report Type layout and add the new fields?
Did you add a Power of One field to each object in the Report and select Summarize(Sum) in the Report Builder?
What is an "Agency" record in the context of your Report?
I think I need to see screenshots of some of these records that show how they do/don't related to each other. Can you verify that there is only 1 record for "Police Dept C" in your org, and no duplicate records?
I am using the power of one formula to count the number of account with at least one closed-won opportunity within a fiscal year. I am able to get the report using a cross object (account -opportunity) report but my next step is to calculate the variance between years.
Im using a summary report, when I add the formula I get an error with the prevgroupval since the power of one field is not grouped.
My question is, is there a way to calculate the variance of power of one field ?
Thanks a lot for your help :)
Could you please post a screenshot of the Report, Report Type, and any Groupings, Settings, etc, that you're currently using and the results you're getting, and create a mock-up of what you would like your Report results to look like? For questions related to Reports and Dashboards those are really helpful
Thanks a lot for your reactivity, that deserves a beer already.
First screen shot is the report and the grouping, as you can see the power of one formula is working well, i am able to count the account with a closed won opportunity every year. Note that "Number of Accounts" is the power of one formula.
Now as a second step i would like to calculate the variance of account number between years, but when i create the formula i cannot use the PREVGROUPVAL as the power of one field is not grouped.
Thanks a lot in advance
You are like a psychologist, you listen, ask questions but I’m the one resolving the issue. Fortunately I can pay you with beer 🍺🙂.
I was able to solve the problem, that actually wasn’t one. I was just not writing the prevgroup formula correctly, nothing is worth a good night of sleep.
1. Variance (Count) Formula
2. Variance (Count) % Formula
3. Variance ($Amount) Formula
4 Variance ($Amount)% Formula
5. The finished Report
🏀🏀 Tip-Off to Success Round 2: Lightning v. Power of 1: 🏀🏀
Our Elite 8 in our Tip-Off to Success Contest has been locked in, and we need your vote!
Which personalization tip do you like better? Who should make it to the Final 4? Vote on your favorite tip here =>
See how it's working for some contacts and not for others? I'm not sure why this is happening. Any thoughts here?
Tip-Off to Success Round 3: Personalization v. Power of 1:
We have groups of overseas travel teams, and I need to be able to count up how many total people are on each team in a field on the team page. I added the Power of One field to Contacts hoping there is some way to use a formula or logic to count up all contacts with the same Team ID and using the Power of One field - any suggestions?
If you need to Roll-Up the Count you'll need to either create a Master-Detail relationship and add a Roll-Up Summary field. Otherwise you'll need to use Declarative Roll-Up by Andy Fawcett (it's free)
Or you'll need custom Apex or Visualforce code
The Power of One is intended to be used in Reports and Dashboards (that's all)
Looking at your initial example there is something that confuses me: when you show your report results
If you sum up the total number of accounts for each owner, the grand total should be 54 (14 + 2 + 1 + 27 + 5 + 5). However the Grand Total for Accounts is 34. For the rest of "the power of 1" columns the Grand Total row seems to be ok.
What is the reason for this discrepancy on Accounts?
I might have missed something...
The reason why you see the (14 + 2 + 1 + 27 + 5 + 5) = 54 in each of the Opportunity Owner Row Groups is because that is the number of Accounts that User has an Opportunity with, not the number of "totally unique Accounts (which is 34).
This is because if Moe has an Opportunity with Initech that's 1 Account in his row group. If Larry has an Opportunity with Initech that's 1 Account in his row group, and If Curly has an Opportunity with Initech that's 1 Account in his row group. Buth there is only 1 Initech Account record.
If you (could) click the Show Details button you would see that there are only 34 "unique" Accounts that have an Opportunity.
Do you also accept cups of wine? In that case I owe you one