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!! 
V360 ConsultantV360 Consultant
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
Tam DuongTam Duong
Hi Steve - Apologize in advance if I skipped this info. in this thread. Does the Power of One field capture historic data or just the data after we created the formula field? Thanks so much! 
Steve MolisSteve Molis
Hi Tam,
Formula Fields like "The Power of One" auto-magically work on any existing record as well as any newly created records
Tam DuongTam Duong
Hi Steve - I couldn't make it to work on my report. I created a formula field exactly per your instruction with the 1 and named it "Campaign Number." When I created a report and drag that new field over, it gives me all blank value. 

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! 

User-added image

Report Builder View
Steve MolisSteve Molis
Hi Tam,

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 
Select: Formula  

Step 2. Choose the Output Type 
Select: Number (0 decimals)

Step 3. Build the Formula 
1

User-added image
 
Steve MolisSteve Molis
This won't work (unless you manually update the Field on every exsiting record on your org)

User-added image
Steve MolisSteve Molis
PS.  Tam Duong you owe Tom Tobin a beer for that  ;-p 
Tam DuongTam Duong
Thank you Steve and Tom! Where are the beer emoji icon on here?! Lol. Thank you all! 
Tam DuongTam Duong
Hi Steve - Yes. I created exactly how you did it. My datatype was formula and output type was set to number and formula build was 1.

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? 
Steve MolisSteve Molis
Hi Tam, 
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

 
Tam DuongTam Duong
Yes, I did! I recreated like you had. 

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. 
Steve MolisSteve Molis
Can you post a screenshot of this Power of One field you Created and the Report that you are using?
Tam DuongTam Duong
Hi Steve - here are what I currently have

Field type = Formula 
Return type = Number 
Decimal Places = 0 
Formula = 1 

I promise I followed exactly the 3 steps. 

User-added imageUser-added image
Steve MolisSteve Molis
Okay, then tiy should be good to go.  Did you delete the old "Number of Campaign" field that you created before that was: 
 
Datatype = Number
Default Value = 1

I would delete and erase that field asap so there is no confusion
Tam DuongTam Duong
Yes, I did. I deleted and recreated a new field. Thank you, Steve! 

p.s. what does "tiy" stand for? :) 
Angela Mullen-SmithAngela Mullen-Smith
Hi
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

User-added image
Steve MolisSteve Molis
@Angela Mullen-Smith
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)
Angela Mullen-SmithAngela Mullen-Smith
Thanks - I appreciate that
Anha SinghAnha Singh
Hello Steve,

It has been wonderfully explained by you. As many times I saw it, I want to thank you again and again. 
Alisyn DiazAlisyn Diaz
Steve,
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.

User-added image
Steve MolisSteve Molis
Hi Alisyn,
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?
 
Alisyn DiazAlisyn Diaz
Thank you for the quick response.  The last field in the report, "MDTObjectPo1" is the power of one field and it is set to Summarize(Sum).  I'm using a custom report type and am able to add the field to the report.  Yes, I added the Power of One field to the agency object.  However, I have not added a Power of One field to the other object that is used in the report, perhaps that is my missing piece?  
Steve MolisSteve Molis
I would strongly recommend adding the Power of One to every object on your org.  

What is an "Agency" record in the context of your Report?  
Alisyn DiazAlisyn Diaz
Agency is an object that was built to capture specific information about our law enforcement agenices.  Each agency has a relationship with one or more counties  (ex: Police Dept C might have a relationship to 'XYZ county' and also '123 county').  Each county is listed in a separate county object.  I do not have county showing in the above screen shot, but it is an available field in the report builder.  I need the report to count each agency only 1 time regardless of how many counties it might have relationships with.  So in the above example, Police Dept C would only be counted 1 time.  I will add the Power of One field to the county object and see if that does the trick.  Thank you!
Steve MolisSteve Molis
Hi Alisyn,
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?
Alisyn DiazAlisyn Diaz
Adding the Power of One field to the account object worked, since there are no duplicate accounts.  Thank you!!
Steve MolisSteve Molis
Thanks, I kinda had a feeling it was gonna be one of the other objects
Julien MontierJulien Montier
Hi Steve, I have been struggling with this for the last hours. I hope you can help me :)

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 :)
Steve MolisSteve Molis
Hi Julien,
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
Julien MontierJulien Montier
Hi Steve, 
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.
Report and grouping

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.Variance formula

I'm stuck

Thanks a lot in advance
Julien MontierJulien Montier
Hi Steve,

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.

🙏 Thanks 
Steve MolisSteve Molis
No problem Julien, glad you got it working.  If anyone else has a simliar need for a Varience Report Formula here's one I did using the Opportunity "Power of One"  
 
1. Variance (Count)  Formula 
User-added image

2. Variance (Count) % Formula  
 User-added image


3. Variance ($Amount)  Formula 
User-added image

4 Variance ($Amount)% Formula 
User-added image

5.  The finished Report
User-added image
Steve MolisSteve Molis
As we say here in Boston "Vote early and vote often" 

🏀🏀 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 =>
http://bit.ly/2pPMsKn
Daniel KaplanDaniel Kaplan
Steve, I'm having an issue here. I'm trying to just show the unique number of contacts that own a certain type of business. Sometimes the power of one works and other times it doesnt. Take a look below:

User-added image

See how it's working for some contacts and not for others? I'm not sure why this is happening. Any thoughts here? 


 
Steve MolisSteve Molis
Can you post a screenshot of the Report and the settings that you are using?
Daniel KaplanDaniel Kaplan
Steve, thanks for getting back to me! I did more digging last night and discovered several duplicate contact records which threw everything off. Problem resolved! 
Steve MolisSteve Molis
It's your last chance to vote for the "Power of One" in the Final 4 Admin Tip Off Contest 

Tip-Off to Success Round 3: Personalization v. Power of 1:
https://success.salesforce.com/_ui/core/chatter/groups/GroupProfilePage?g=0F9300000001p8w&fId=0D53A00003Sw2we
Shauryanaditya SinghShauryanaditya Singh
Hi... I had forgotten about this rule. You are cordially invited to India for a beer. Steve Molis :)
Marissa MMarissa M
For some reason roll-up fields don't work for my custom object which is a custom object related to Contacts through a lookup relationship field on the Contact page.

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?
Steve MolisSteve Molis
Hi Marissa,
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) 
https://andyinthecloud.com/2016/06/19/declarative-rollup-tool-summer-release/ 

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)
Marissa MMarissa M
Thought that might be the case Steve, thanks for the clairty! 
Jaime AcostaJaime Acosta
Hello Steve, thanks a lot for this.
Looking at your initial example there is something that confuses me: when you show your report results
User-added image

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...

Thanks! 
Steve MolisSteve Molis
Hi Jamie,
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.    
Jaime AcostaJaime Acosta
I see... now it makes total sense. Thanks!
Do you also accept cups of wine? In that case I owe you one
Steve MolisSteve Molis
Sure!  I like a nice Sonoma Red (almost) as much as a West Coast IPA
Clay TClay T
I work for a very large company and have admin rights to create fields like this... currently we do not have this on our objects.Problem being we have a whole team dedicated to managing Salesforce fields. I think I'd get rejected if I asked to have this implemented. Thinking about doing it and hoping noone notices.
Steve MolisSteve Molis
Just do it, and if anyone asks just tell them that it was part of the Salesforce "Autumn Release" 

Autumn'17 Release Notes
https://success.salesforce.com/0693A000006pnba
Steve MolisSteve Molis
I'm just gonna leave this right here... 

User-added image
Karolina TouwenKarolina Touwen
hello Steve, if I would like to count unique cases per edited by from case history. So, not only count unique by one column but by two columns. How to do that?
Steve MolisSteve Molis
Hi Karolina Touwen,

Could you 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) 
 
Karolina TouwenKarolina Touwen
hello Steve, I am trying to calculate 
Minimum(closed cases, incoming cases))/(incoming cases)
I have made this matrix report, how to add forumula which could divide the grand total of row by grand total of column?
User-added image
Steve MolisSteve Molis
So usinging the numbers in your Report screenshot what results are you expecting?  

Can you create a mock-up of what you would like your Report results to look like? 
Karolina TouwenKarolina Touwen
I would like to know how to make formula in this matrix calculating the following

=min(1113,1420)/1420, this should result in 78%
=min(1466,1510)/1510, this should result in 97%
Karolina TouwenKarolina Touwen
I would lkie to knwo what the ratio of open vs closed cases is. I can group in matrix closed cases by closed date and open cases by open date. But how to divide tthem to have the ratio? They are in grand total of the matrix. how to divide grand total of the row by grand total of the column?

Or should I use another report, like joint, but how to group? or maybe there is another way to have open vs closed cases
Matt BostromMatt Bostrom
would this work in the case where i am reporting on Quote Lines and I need to show the number of quotes that have this particular product without double counting the quote? i stil think it will double count in this case right? e.g. a quote line has a product listed twice on that particular quote. however i want to show quote count summarized = 1. if i put the power of 1 on the quote header or the quote line wouldnt it erronesouly show that the quote line is on 2 quotes and not 1? i am currently grouping by the quote line product name and average gross margin % from the CPQ quote object. the details show the quote lines and some additional detail but i can't seem to show the total quote count the product is on (if the product is on the quote twice). so i need a unique quote number somehow but from the quote line details.
Steve MolisSteve Molis
Hi Matt,
Could you 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) 
 
Matt BostromMatt Bostrom
Hi Steve,
Sure. I have blurred out confidential company costs and product names but you can get the jist of what i'm trying to do. Our GM% needs to be line specific b/c each quote line has a certain cost and discount applied so we cannot aggregate. I need to show the product and count the unique quotes those lines are on (not double count a quote twice). see screeshot:

User-added image
Steve MolisSteve Molis
Hi Matt B,

Sorry I was off the grid for a while, then playing catch-up with work.  

Which Report Type is that?  I don't use anything like that on my org, and I need to wrap my head around which objects are in it and how they relate to one another
 
 
Steve MolisSteve Molis
PS.  Did you add a Power of One field to every object in that Report and Sum each of them?
Steve PetrieSteve Petrie
Steve, could I use this to show the number of accounts my reps are logging activities for each month? 
Steve MolisSteve Molis
Steve Petrie 
Yes, but it depends on the Report Type.  It needs to be a Report Type that can access the Power of One field on the Account object, like "Activities with Accounts" or "Activities with Contacts", "Activities with Opportunities", or something like that
Steve PetrieSteve Petrie
Ok perfect....we are trying to see how many accounts our reps are logging activities for each month, but account ownership changes quite frequently so account owner doesn't always equal activity owner. Ideally looking for an activity report where the rows are the "assigned" field and we're able to summarize number of unique accounts the rep has logged activity for that month
Steve PetrieSteve Petrie
Actually, I'm not sure that will work......any other work ways to achieve this Steve Molis?
Steve MolisSteve Molis
What do you mean "it won't work"? 

Could you 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)
Reema KawatraReema Kawatra
Yay!!! It works! Thank you Steve Molis!
Reema KawatraReema Kawatra
Hi Steve, its not working for one the reports. Can you have a look please. Sum is incorrect. Thank you!
User-added image
Steve MolisSteve Molis
What numbers in the Report are incorrect? 
And what should the correct numbers be?  
Which Report Type are you using? 
Is that a Custom Report Type? 
If it is a Custom Report Type, can you post a screenshot of the Object Relationships in the Report Layout?
Which objects are each of the fields in your Report screenshot located on? 
 
Reema KawatraReema Kawatra
What numbers in the Report are incorrect? Sub-Total 
And what should the correct numbers be?  3
Which Report Type are you using? Custom Report Type
Is that a Custom Report Type? Yes
If it is a Custom Report Type, can you post a screenshot of the Object Relationships in the Report Layout? Yes, please see below. Related Products is a junction between P&F Details and Product Repository objects.
Which objects are each of the fields in your Report screenshot located on?
Opportunity Name from Opportunity object & Product:Account Name from Product Repository object. An opportunity can have multiple P&F Details records and a P & F Details record can have multiple related products. Each Product is associated to an Account. We want a report on how many Product Accounts are covered by each opportunity.

User-added image
Steve MolisSteve Molis
That Report looks like you're summarizing the Power of One field from the Account field on the Opportunity, not the one from the Product: Manufacturer's Account 
Reema KawatraReema Kawatra
I chanhed it to Product: Manufacturer's Account but now i'm not able to sum it.
User-added image
Steve MolisSteve Molis
It looks like you just added it as a Report Grouping (that's not gonna work) you need to add it the Report just like you did with the other Power of 1 field and select Summarize(Sum) like in the screenshot at the very top of this post
Reema KawatraReema Kawatra
Its working now!!! Thank you so much! You're awesome!
Peter BaldwinPeter Baldwin
Steve, I'm stuck; I've applied The Power of One but can't seem to get a simple percentage from a Summary Field Formula:
intended summary formula
I trust you'll get me there, and as I think you're in Beantown I'll owe the real deal (no cyber beers), maybe from the Wachusett Beer Garden...
Peter BaldwinPeter Baldwin
By the way those three columns are bukets; may have something to do with my dilemma?
Steve MolisSteve Molis
Hi Pete, I can't take a look at it right now, I'm on my mobile phone. Won't be online for a while
Steve MolisSteve Molis
Pete I can't see your screenshot images, can you try re-uploading them?
Steve MolisSteve Molis
Nevermind just had to wait for my browser to catch up
Steve MolisSteve Molis
Pete, are you looking for something like this?
1
User-added image
2
Here's the Formula and sesttings that I used 
User-added image
Ashley UnderwoodAshley Underwood
Can I get this for the lighting addition?
Steve MolisSteve Molis
Lighting, Classic, Thunder, it doesn't matter what you're using "The Power of One" works on all of them
Peter BaldwinPeter Baldwin
Exactly Steve, but I'm getting a "field does not exist" message... let me go through it again and document it; will try to get back to you as soon as I can, thanks
Steve MolisSteve Molis
@Peter Baldwin 

What do you mean?  Where are you seeing that error?  Did you create The Power of One fields on all of the Objects in the Report?  
Can you post a screenshot of where you're getting this error?
Steve MolisSteve Molis
PS.  @Peter Baldwin I'll keep your tab open over at "Who owes me a beer?"
Michele LougheedMichele Lougheed
Hi Steve. Can I use this field to create a report that shows all Accounts without any Contacts in a specific campaign?

I created a custom report type for Accounts > Contacts > Campaign History and added the Power of 1 custom field to Accounts, Contacts and Campaigns, but am not able to add the field to this report type. I was expecting to be able to add a cross-filter for Accounts without Contacts > Contacts without Campaign History > Campaign Name equals xx, but haven't been able to and was hoping your Power of 1 field would address what I'm trying to do. Thanks for your help.
Peter BaldwinPeter Baldwin
Okay, I'm back; giving it a go with minor mods, and will give you more details if I can't get it to run

new target data
Peter BaldwinPeter Baldwin
So here's my fail:
summary formula fail
Financial Plan Review is bucketed under Client Tiers (each of the three columns); neither Activity:Financial_Plan_Review__c:sum nor Task:Financial_Plan_Review__c:sum works (same syntax error); and am I using the right form for the bucketfield at the end of the ParentGroupVal statement?
Peter BaldwinPeter Baldwin
... Getting warmer!
summary formula success
I've been typing the field names in; that's when I got the fails; when I use the drop-down "Summary Fields" from the picklist it worked; arghh!
Steve MolisSteve Molis
Yeah Pete, I never type in Field Names, you're only looking for trouble. 
Steve MolisSteve Molis
@Michele Lougheed 

Did you update the Custom Report Type Layout after you added the "Power of One" fields to the objects?  Custom Report Types do not auto-magically recognize new fields when you create them, you need to edit the the Custom Report Type Layout and manually add new fields.
Peter BaldwinPeter Baldwin
Yup; and done. Woohoo!

summary formula final
Michelle VoseMichelle Vose
Looking for suggestions on getting a specific count, based on the value of '1' rather than the RowCount; similar to a Count IF, which I've only been able to find an Idea about.

I had the administrators create a Power of One for me to count the Passives, Promoters & Detractors.
The Detractors & Promoters then needs to be divided by the total number of responses to the question, no the number of survey responses.  So in this case, I need the calculation to be 100/732=%  and 295/2189=% for both Detractors and Promoters.

User-added image
"% Detractors"  Survey_Feedback__c.Detractors__c:SUM/RowCount
"% Promotors"  Survey_Feedback__c.Promotors__c:SUM/RowCount 
Steve MolisSteve Molis
@Michelle Vose

Which object did you create your "Power of One" fields on?  Did you also create just a Power of One field that simply gives you the unique record count?
Steve MolisSteve Molis
@Michelle Vose

If you have a Power of One field for the Record Count your formula would be something like this 
( Survey_Feedback__c.Detractors__c:SUM 
/
(Survey_Feedback__c.Survey__c:SUM)

otherwise you'll need to add all of those fields you created like this 
IF( (
(Survey_Feedback__c.Detractors__c:SUM + 
Survey_Feedback__c.Promotors__c:SUM + 
Survey_Feedback__c.Detractors__c:SUM)  = 0, 0 ,

( Survey_Feedback__c.Detractors__c:SUM 
/
(Survey_Feedback__c.Detractors__c:SUM + 
Survey_Feedback__c.Promotors__c:SUM + 
Survey_Feedback__c.Detractors__c:SUM) 
) 
)
  

 
Michelle VoseMichelle Vose
I've reqAdvanced Administrator to provide me with the details on how he created the fields.
Michelle VoseMichelle Vose
@Steve Molis, this is what he used to compile the counts.  I don't think he used the Power of One as recommended.  However, we did got it worked out.  Apparently it just needed additional filters to disregard the records which had no results in the NPS_c field.  Now the % calculations are accurate.  Thanks for your time.

Detractors:
 IF( OR(NPS__c = 0,
NPS__c = 1,
NPS__c = 2,
NPS__c = 3,
NPS__c = 4,
NPS__c = 5,
NPS__c = 6),1,IF(NPS__c = null,null,0))
 
Passives:
 IF( OR(NPS__c = 7, NPS__c = 8),1,0)
 
Promotors:
 IF( OR( NPS__c = 9, NPS__c = 10),1,0)
 
Steve MolisSteve Molis
Wait...  is that a Formula field on the object? 

I thought you were trying to do this in a Report Summary Formula?
Nadezda UpenieceNadezda Upeniece
Hi Steve,

Thank you for your greatest help with this formula!

I am trying to use it, but it doesn't seem to work.

I have a field (Svar), which is a number, and I would like to have a total of those values, not records.. How can I achieve it?

User-added image
Steve MolisSteve Molis
Thanks Nadezda Upeniece 

Did you select Summarize(Sum) in the Report Builder like I did in the third screenshot that I posted? 
User-added image
Nadezda UpenieceNadezda Upeniece
Yes I do
 User-added image
Steve MolisSteve Molis
So what do you see when you runn the report?  (your first screenshot was the Report Builder Preview)
Nadezda UpenieceNadezda Upeniece
User-added image
Steve MolisSteve Molis
Okay which object is the "Svar" field located on?  

Which Report Type are you using? 

Using your report screeenshot as an example, what number are you expecting the Sum to be?
Nadezda UpenieceNadezda Upeniece

Svar is under the same Object with Svar Total. I am using custom report type and I have already updated it. 

I am expecting this SVAR counting all values (10 + 2 + 4x2 = 20) 

It seemed to work before, and then I have made some changes and it doesn't anymore. Don't know where to start again..

Steve MolisSteve Molis
btw - I just did a Google traslation, is "Svar" the Swedish word for "Response", is that correct? 

What is a Svar/Response?  Is each Svar/Response it's own unique record?  Are they related to another record in this report, like an Account, Contact, or Opportunity?  

Which object are being referenced in this Report?  


PS.  Sorry, the only language that I ever learned is English, and I barely passed English class in school 
Nadezda UpenieceNadezda Upeniece
Yes, it is. It's custom object with questionnaire, and SVAR is the answer. So the questionnaire has answer types as numbers, and I would like to count the total of those answers.
Nadezda UpenieceNadezda Upeniece
There are 10 unique records of SVAR (1,2,3,4,5,6,7,8,9,10)
Steve MolisSteve Molis
Okay, I think that you might have 2 different fields in this report that are both named "Svar", one of them is a "Power of One" field, that's the one that is returning a value of 1 on each record, and a total of 4.  

I don't know what the other "Svar" field is, but that has the 3 values: 10, 2, 4 in it.   To get the SUM of that Svar field you'll need to create a Report Summary Formula, or remove it  as a Report Row Group and summarize it like you're doing with the Svar "Power of One" field
Steve MolisSteve Molis
Ahhhhh!   Okay to do that we'll need to do one of the things that I suggested
Nadezda UpenieceNadezda Upeniece
I have removed it from Row Group, but Summarize is not available for me.

User-added image
Steve MolisSteve Molis
What is the Datatype of the Field?  Is it Text or a Picklist and not a Number? 
Nadezda UpenieceNadezda Upeniece
Text.... Should that be a Number? Do you think that could be the reason?
Steve MolisSteve Molis
Yes, that's it.  You can't perform Math (like SUM) on the Text or Picklist datatype field, you can only do it on Number, Currency, Percent, or Boolean.  

But I think I have a fix for you...  just gimme a sec 

PS.  Do you have permission to create custom fields on your Salesforce, or do you need to request them?
 
Nadezda UpenieceNadezda Upeniece
I can do it. 
Nadezda UpenieceNadezda Upeniece
I have already changed it to Number. All data on it is gone. Good, that it's a test environment. :D
Steve MolisSteve Molis
Oh!  I was gonna suggest something that would now cause you to lose anything... 

Create a new custom field
Select 
Datatype = Formula
Result = Number
Formula = 
VALUE( Svar__c )
Insert the "Text" Svar field into the Formula
 
Nadezda UpenieceNadezda Upeniece
Thank you!  Now I know :D 
Steve MolisSteve Molis
PS.  @Nadezda Upeniece You owe me a beer!  ;-D
Nadezda UpenieceNadezda Upeniece
Wow... Thank you so much! I wish I not only owed you a beer, but I really would get you the best beer possible. Really appreciate the effort. Thank you, Steve.
Steve MolisSteve Molis
No problem, if you're going to Dreamforce you can come to my session with one! 
Peter BaldwinPeter Baldwin
Hey Steve, I'm baaack...

Applied the Power of One but now have another take on grouped data. I've applied the following:

Activity.Financial_Plan_Review__c:SUM
/
PARENTGROUPVAL(Account.Account_1__c:SUM, Account.Lead_Wealth_Advisor__c)

Which results in the following:

grouped count percent errs

I'm thinking what I need to incorporate is another Power of One field specific to the grouped text picklist. How might I go about that when making the field, and then how does the formula field execution? I can bucket the fields and get the values pulled apart [Sept. 17 above], but then I cannot generate a graph [which is why I'm going about it this way].

Looking forward to our beer together!

Thanks,
Pete
Peter BaldwinPeter Baldwin
Nevermind; got it to chart after removing one of three group levels. Just the same, how might I have created a formula field to achieve the above?

Best,
Pete
Steve MolisSteve Molis
Hi Pete, 
I'm at Dreamforce all this week, so not online much.  Using the numbers in your Report screenshot, what result were you expecting your Report Formula to return?   

Which Report Type are you using?
Jacklyn BentonJacklyn Benton
Hi Steve,

I am geeked to add this to our reports! Are you able to confirm whether or not the "Power of One" sums will show on the actual chart in Dashboards?
User-added image

I have been messing with it for a while and can't seem to figure it out.
User-added image
Steve MolisSteve Molis
@Jacklyn Benton 

Yes, but you would need to select the "Power of One" Sum instead of $Amount, or select a different Chart Type
Peter BaldwinPeter Baldwin
@Steve Molis

As mentioned above I was looking to pull the denominator from the row, and not the total, as my formula from 9/26 had shown. So in that example I wanted 5 of 15, not 5 of 105.

Best,
Pete
Steve MolisSteve Molis
Okay so you want a Formula like this:

(Checkbox:SUM / Power_of_One:SUM)

at each Client Tier Grouping in your Report?
Peter BaldwinPeter Baldwin
That's it. 

I owe you a beer.
Seth PartridgeSeth Partridge
Still just as good as it  was 9 years ago. Boom!
Sarah MayerSarah Mayer
Is anyone else unable to summarize their field? I created an Account with Contacts report, and I want to see from the Contact address, how many Accounts we have in each state. 
Steve MolisSteve Molis
Hi @Sara Mayer

Could you 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)
Jonny KatzJonny Katz
How can I add a % of won and the "closed" field is total closed opps including won correct? 

User-added image
Steve MolisSteve Molis
@JK
Are the Won and Closed fields in your Report the standard Won and Closed fields on the Opportunity object, or custom fields? 

The Standard "Closed" field counts any Closed Opportunity Stage (Closed Won + Closed Lost) 
Jonny KatzJonny Katz
Both look like standard.
User-added image

User-added image
Steve MolisSteve Molis
Okay then just create a Report Summary Formula like this
IF( CLOSED:SUM > 0 , ( WON:SUM / CLOSED:SUM ) , 0)

 
Jonny KatzJonny Katz
sorry if this is a stupid question but i am basing this report on opp create date right?
Steve MolisSteve Molis
I dunno what you mean by that =>  "i am basing this report on opp create date"
Jonny KatzJonny Katz
User-added image
Steve MolisSteve Molis
That's just a screenshot of a  Report Filter, which "Created Date" field is that referecning?.  

Which Report Type are you using? 

In this screenshot you posted earlier you're showing Fields from the Lead Object.  Are you using a "Leads with Converted Lead Information Report"?  Or are you using a Custom Report Type? 

User-added image

 
Jonny KatzJonny Katz
Thats the other report i am working on which you are helping me with. This is a seperate power of 1 report which i am building. the report you are referring to is https://success.salesforce.com/answers?id=9063A0000019hHK
Steve MolisSteve Molis
Wait... so these are both the same question?
Jonny KatzJonny Katz
seperate questions and serperate reports.
Steve MolisSteve Molis
Okay then let's just talk about THIS report... 

What are you trying to do in this report? 

Explain the requirements, the filter criteria, and the goal 
Yuri MirabalYuri Mirabal
Hi Steve, even though your post is great I've been having a hard time with a report. I applied the Power of One but I don't seem to make it work. I'm working on a Summary report that should look like this:
User-added image
I applied the Power of one to Opportunities, but did not work. I also tried with Roll Up field on Accounts pointing to Opportunities, but nothing.

This is the configuration I have so far in the report:
User-added image

And this is the result:

User-added image

But the numbers don't match.
Thanks in advance for your help.
Steve MolisSteve Molis
@Yuri Mirabal 

Let's start with the Report Types that you are using.   

Also, using the numbers in your Report Results can you you explain what it "wrong" or "not working"?  

 
Yuri MirabalYuri Mirabal
It's a Summary report groupped by Opportunity Owner. I don´t mind the Grand Totals not matching at the bottom. The Opportunity Lost doesn´t match, it´s a Roll-Up Count field on Accounts and Opportunity as Summarized object with the condition of Close = True and Won = False. Won column I picked it from the options in the report, but doesn't match either with the values I have in the system
Steve MolisSteve Molis
@Yuri Mirabal

Can you post complete screenshots of the Report, the Report Type, and the Filters, and Settings that you're currently using and the results you're getting?

Can you also post a screenshot of the records that are "missing" from the Report? (for questions related to Analytics: Reports and Dashboards those are really helpful).  

There are basically 3 reasons why something doesn't show up in a Report or Dashboard: 

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
Emma LlewellynEmma Llewellyn
Sorry to jump on a thread but I have a different (related) question for @Steve Mollis.
I have an Opportunies with Products report. Report is grouped by Opportunity Owner and Opportunity Name. I've added my Power of 1 field in the report, and in the report, it correctly counts the number of Opps, not the total number of Opportunity Product Lines. Screenshot below. 
However, when I add this report to a Dashboard (Lightning Table, grouped by Owner, displaying Sum of Oppo Count & Sum of Total Value), and add the Power of 1 field, it insists on counting the total number of Opportunity Product lines instead. Screenshot below. 
Any idea how I can correct this please? User-added imageUser-added image
 
Steve MolisSteve Molis
@Emma Llewellyn 

I've seen some wonky stuff with Lightning Dashboard Tables, can you post a screenshot of the DB Table Settings that you are using?
Emma LlewellynEmma Llewellyn
Hi @Steve
Thanks for getting back to me, these are my dashboard settings: 
User-added imageUser-added image
Steve MolisSteve Molis
Do me a favor and try hiding the Record Count
Jacklyn BentonJacklyn Benton
How do I unfollow this question. Thanks, Jacklyn L. Benton Sales Coordinator/Graduate Trainee ... Smurfit Kappa North America 125 E. John Carpenter Fwy. Irving, TX 75062 ... Mob: (972) 269 1199 Fax: (469) 680 6061 www.smurfitkappa.com Follow us on LinkedIn | Twitter Check out our microsite: www.openthefuture.info IF YOU PRINT THIS EMAIL, PLEASE RECYCLE IT. PAPER IS RENEWABLE AND RECYCLABLE
Emma LlewellynEmma Llewellyn
It already was hidden: 
User-added image

I've unhidden it just to see what happens. Weirdly at first it displays correctly (record count 7, oppo count 3), but then as soon as I refresh it goes back to this: 
User-added image
Steve MolisSteve Molis
@Jacklyn Benton 

Did you try clicking the Unfollow link at the bottom of the original post? 
User-added image
Steve MolisSteve Molis
Okay there is DEFINITELY something wonky going on, I just had the same thing happen to me too.  I have to try a few times starting fresh with a "new" Dashboard Component and turn show/hide Record Count on/off a couple of times, but eventually it did update the Dashboard and display the correct results 
1
User-added image
2
User-added image
Emma LlewellynEmma Llewellyn
Thanks Steve, will give that a go. Appreciate you looking at it for me :)
Steve MolisSteve Molis
No problem Emma, it might be worth opening a ticket. 

PS.  You owe me a beer!  ;-D
Emma LlewellynEmma Llewellyn
I thought I might :)
Jacklyn BentonJacklyn Benton
It says I’m not following it [cid:image001.png@01D48573.2AE65B60] Thanks, Jacklyn L. Benton Sales Coordinator/Graduate Trainee ... Smurfit Kappa North America 125 E. John Carpenter Fwy. Irving, TX 75062 ... Mob: (972) 269 1199 Fax: (469) 680 6061 www.smurfitkappa.com Follow us on LinkedIn | Twitter Check out our microsite: www.openthefuture.info IF YOU PRINT THIS EMAIL, PLEASE RECYCLE IT. PAPER IS RENEWABLE AND RECYCLABLE
Steve MolisSteve Molis
@Jacklyn Benton 

I dunno kid, there's nothing that I can do from this end, being the person who posted a Question isn't like I'm the Moderator or Owner of a Chatter Group.  I can't remove you or anything like that.  
Jacklyn BentonJacklyn Benton
Haha – thank you anyways! I will take care of it somehow. Thanks, Jacklyn L. Benton Sales Coordinator/Graduate Trainee ... Smurfit Kappa North America 125 E. John Carpenter Fwy. Irving, TX 75062 ... Mob: (972) 269 1199 Fax: (469) 680 6061 www.smurfitkappa.com Follow us on LinkedIn | Twitter Check out our microsite: www.openthefuture.info IF YOU PRINT THIS EMAIL, PLEASE RECYCLE IT. PAPER IS RENEWABLE AND RECYCLABLE
Drew BurksDrew Burks
@Steve Molis Is there a way to count the number of summary rows if a report is not summarized by an object, rather by a picklist.  I'm trying to report on the number of values used in each state, but can't seem to get that working.  Specifically I am trying to do this in Einstein Analytics.  Thanks.
Steve MolisSteve Molis
@Drew Burks  

I don't have any hand's-on with Einstein Analytics  
https://www.youtube.com/watch?v=5lL1ypndnWA&feature=youtu.be&t=52s
  
Could you 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 (otherwise it's kinda like playing "Pin the Tail on the Donkey"). 
 
Dan KuzniewskiDan Kuzniewski
I can't get this to work.  I have created Power of One fields in the Account and Opportunity object.  That seems to be working, but I'm getting 0's for Closed and Won.

User-added image
Steve MolisSteve Molis
Okay, first of all, you don't name your Power of One fields "Power of One" ( I specifically mentioned that in my post). Go back and rename them after the Objects they're on and re-run your report and post an updated screenshot
Steve MolisSteve Molis
PS and check your Report filters to make sure you're not excluding them
Dan KuzniewskiDan Kuzniewski
Is the name of the fields affecting its accuracy?  I’m testing this out. And not excluding them. 
Steve MolisSteve Molis
No, but it makes it nearly impossible to tell what you are counting.
Steve MolisSteve Molis
Also, if Tom Tobin says to do something, it's a pretty good idea to do it.
Dan KuzniewskiDan Kuzniewski
Ok. I can adjust that. But any ideas about why closed and won and 0?
Steve MolisSteve Molis
Can you post screenshots of the Report, the Report Type, and the Filters, and Settings that you're currently using and the results you're getting? Can you also post a screenshot of the record that is "missing" from the Report? (for questions related to Analytics: Reports and Dashboards those are really helpful).  

There are basically 3 reasons why something doesn't show up in a Report or Dashboard: 

1. It does not meet the scope of the Report Type or filter criteria being used.
2. The User does not have at least Read access to it
3. It does not exist
Steve MolisSteve Molis
@Dan Kuzniewski 

Any luck you with the new report and screenshots?
Dan KuzniewskiDan Kuzniewski
Report, Report type and Filters attached.  What exactly fo you mean by settings?

User-added image
User-added image

User-added image
Steve MolisSteve Molis
Last night I asked you to check your Report filters to make sure you're not excluding them. 

And you replied  "I’m testing this out. And not excluding them."   

This IS excluding them =>
User-added image

Like I said, there are basically 3 reasons why something doesn't show up in a Report or Dashboard: 

1. It does not meet the scope of the Report Type or filter criteria being used.
2. The User does not have at least Read access to it
3. It does not exist

Closed and Won are not "Open"
User-added image
Steve MolisSteve Molis
@Dan Kuzniewski  

PS.  You owe me a beer!
Dan KuzniewskiDan Kuzniewski
I get the same result when I run with a status of "any".  And I cann't remove opportunity status from the filter.
Steve MolisSteve Molis
Okay, last night I aslo asked you to post a screenshot of a Closed/Won record that is "missing" from the Report.

Can you also add the Opportunity Stage to the Report and Group the Report by Stage instead of Owner and re-run the report and post a screenshot?
Steve MolisSteve Molis
@Dan Kuzniewski  

Also, not for nuthin' but the fields "Won" and "Closed" have absolutely nothing to do with The Power of Won.  They are standard fields, driven by the Opportunity Stage settings.  Every Opportunity Stage (no matter how many Stages, or Sales Processes you have) is either Closed or Not Closed, Won or Not Won. 

So you might wanna pop the hood and take a peek inside 
 
User-added image
User-added image
Steve MolisSteve Molis
@Dan Kuzniewski  

Any luck with that new Report and the screenshots?
Steve MolisSteve Molis
@Dan Kuzniewski  

Do me a favor, set up the report exactly like this: 
 
Filters: 
  • Show Me =All opportunities
  • Close Date = All Time
  • Opportunity Status = Any
  • Probability = All
Grouped by Opportunity: Stage  
 
Re-run the Report and post a screenshot like this
User-added image
Andrew MorseAndrew Morse
I thought I might leverage the "Power Of One" as a lightning page conditional filter. My goal was to hide a single related list component if the Power of One was 0 (no related records) but the formula field isn't available as a filter field? Any ideas?
Andrew MorseAndrew Morse
Never mind, I figured it out. The related records are not child records. Boo! Hiss!
Steve MolisSteve Molis
@Andrew Morse is for Reports only
Sarah JoySarah Joy
@Steve Molis
Is Po1 the approriate solution when trying to sum only a certain account record type? I am trying to figure out how many broker accounts each of my sales reps is quoting with. I am using an opportunity with contact roles report and dont want to count the contacts (individual brokers), but the broker firm accounts they belong to. The developer and i tried adding Po1 to the account page, but the sum only used the accounts the opportunities were attached to. Please help! 
Steve MolisSteve Molis
Hi @Sarah Joy,
Can you provide more detailed information about the Object relationships and the Report Type that you're using. 
how are the Broker Accounts attached to the Opportunity?  Are they attached via Contact Roles?   
Harshal PatilHarshal Patil
Hi steve ...
i'm confusing about power of one formula please help me
 
Steve MolisSteve Molis
@Harshal Patil 

Can you please elaborate and provide some context?
Harshal PatilHarshal Patil
how its work and where we can use this formula ??
Steve MolisSteve Molis
@Harshal Patil 
If you go up to the very top of the tread I explain how to create it with step by step instructions, and how to use it.  I don't know if I can get any more detailed than that.  

Is there something "specific" that you are having trouble with?
Harshal PatilHarshal Patil
i want to calculate sum by contact name is it possible 

User-added image
 
Harshal PatilHarshal Patil
sorry i want to calculate sum by account name is it possible
Steve MolisSteve Molis
@Harshal Patil 

What do you mean by "calculate sum by contact name"? 

Calculate the Sum of what?  
The Sum of Opportunties?
The Sum of Activiites?
The Sum of something else? 

Also, in your screenshot you're using an Accounts Report, that Report Type will not have any Contact information in it.  So if you want to sum anything related to Contacts you will need to start by selecting a different Report Type
 
Harshal PatilHarshal Patil
sorry i want to calculate sum by account name is it possible
Steve MolisSteve Molis
@Harshal Patil 

I'm sorry but I don't understand what you mean by "calculate sum by account name" 

"Sum" implies a numeric or currency value, something that can be counted and summed.  Account Name is a Text value, you can't count or sum text.  Also, in your report screenshot I see a custom field called  "User Account".  I'm not sure what that field is supposed to be.  Is that supposed to be the count of Account records?  The count of Account Owners(Users)? or something else?

Can you please provide a more detailed explaination and some context? 
Harshal PatilHarshal Patil
oh okay. 
so i can calculate account records by Account Owners (power formula filed) 
right ?
and its need to create power of one formula on user object or anywhere else ??
and as per your guide i cant calculate text filed so "calculate sum by account name" it's wrong.
 
Steve MolisSteve Molis
@Harshal Patil  

Yes, you need to create a separate "Power of One" field on each object that you want to count: User, Account, Contact, Opportunity, Activity, Lead, Case... 

As a personal best practice I name each "Power of One" the Object that I am creating it on: ser, Account, Contact, Opportunity, Activity, Lead, Case... 

Then I summarize the Report by the Field that I want return the summary counts, like Account Owner.  And select my User and Account Power of Onw fields and select Summarize(Sum)

Like this:
User-added image

PS.  It is after 1:00 AM here and I have to get some sleep before work.  I do not know how else I can explain the Power of One beyond what I have already written.  
Harshal PatilHarshal Patil
Great... i got it now.

Thank you so much for your grand support.
Have a great day a head :)
sakshi nagpalsakshi nagpal
cool stuff
Steve BalinskiSteve Balinski
I dont understand what this does?  Or furthermore what happens if you don't do this.
Steve MolisSteve Molis
@Steve Balinski 

This is a standard Opportunity with Productss report using the standard report "Record Count". 

The Record Count shows you that there are 866 records in the report. 

But it does not tell you how many Users owne an Opportunity, How many Accounts have Opportunties, How many Opportunties you have, or how may Products are being sold 
User-added image


Here is the exact same report after adding "The Power of One"

I can now see that 6 Users have Oppotunties, 70 Accounts have Opportunities, I have 715 Opportunities, and there are 269 Opportunity Line Items
User-added image
 
Steve BalinskiSteve Balinski
you had to add in the User, Account, Opportunity, Opportuniyt product columns though right? What would those have shown before?
Steve MolisSteve Molis
Nothing, those are the names of the Power of One fields that I created on the User, Account, Opportunity, and Opportunity Product object as I mention in my post at the top of this thread.  I'm sorry if I was not clear, I don't write training materials for a living (thankfully) 
Steve BalinskiSteve Balinski
Did you add a "power of 1" column for each of those?  just trying to understand what you added to the report that generated those columns, typically you have to select a column for each and add it.
Steve MolisSteve Molis
@Steve Balinski 

I think you need to go back and re-read through my orignal post at the top.  I walk you through what I did step-by-step with screenshots.  I dunno how much more I can do than that, like I said "I don't write training materials for a living".   
Steve BalinskiSteve Balinski
I saw where you say "Grab a Report and add your shiny new "Power of One" fields to it and select Summarize(SUM) ", but that adds all 4 of those columns to the report in your screenshot?
Steve MolisSteve Molis
@Steve Balinski

Sorry, you lost me... 

Maybe you should explain what it is that you're trying to do? 

 
Steve BalinskiSteve Balinski
Steve, you already helped me on another report question I posted today.  On this example you just provided you have 4 columns, "User, Account, Opportunity, Opportunity product"  Did all 4 of those get added just by adding 1 field for Power of One?  Or do you need to add a Power of One field for each column
Steve MolisSteve Molis
You create the Power of One field on each object (like I say in my very first post at the top of this thread)

"...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). "
 
Steve MolisSteve Molis
I finally got around to creating an Unmanaged Package that creates Power of One fields on nearly every Standard Salesforce object (you're welcome) 

The Power of One
 Version 1.0

 Use this URL to install the package into any organization:
https://login.salesforce.com/packaging/installPackage.apexp?p0=04t4P000001cdjK

Password = UoweB33R

Note: If you are installing into a sandbox organization you must replace the initial portion of the URL with http://test.salesforce.com
Dianna D'AlessandroDianna D'Alessandro
Can you use power of 1 in report criteria?  For example, I've build Power of 1 with Leads with Activities (report type). I only want to see for those lead records those that only have 1 or 2 activities logged. Is this possible? User-added image
Steve MolisSteve Molis
No, it doesn't really work as a report filter like that
sakshi nagpalsakshi nagpal
Great idea, thanks for sharing!
William StreetWilliam Street

Hi all, would the power of one report also work for a task report?

We use Hubspot with SalesForce and when a new MQL comes through from Husbpost, a task is created against the Lead/Contact.

I have built a report for the new lead and contact MQL's for the month but the only time it falls down is when the Lead/Contact makes more than one enquiry within the month (i.e. 2 or more tasks for the Lead/Contact which blows out the numbers count) If I can make the report count just the Unique Leads and contacts regardless of how many enquiries they make it is will be mission accomplished in counting leads and contacts in one report.  

 

I need a way of just counting the sum of the leads contacts for the month just once without counting the extra tasks. SalesForce has openly admitted and has not done anything about a Lead/Contact report as yet, I feel the task report is the next best thing without buying add-on software.

Would love to hear anyone's thoughts on this.
 

 

 

Steve MolisSteve Molis
Which Report Type are you using?  Are you using Activities with Leads and Activities with Contacts?  

Or are you just using and Activity (Tasks & Events) report?  

It will work with an Activities with Leads and Activities with Contacts report, but not an Activity (Tasks & Events) report
Lisa PutnamLisa Putnam
I am jumping in with my own Power of One question. I need to be able to deliver a count for each conference relationship in this report. The first screenshot is the report in Salesforce without knowing how to count the number of each "Conference Relationship" in each district. The second screen shot is a report I wrote in Access using similar data that gives me the counts I need. Is the Power of One useful in this situation or is there another way?
User-added image
User-added image
 
Steve MolisSteve Molis
@Lisa Putnam

What are the Object relationships in your Report screenshot?  

How many unique "Retired Elder" records are there? I count 7 in your Report (2 in the NC District)  are my numbers correct? 
Lisa PutnamLisa Putnam
Hi Steve! On the SF screenshot I count 6 unique "Retired Elder" records across 4 districts and 2 in the NC district.  The "Conference Relationship" field is from the Appointments object and the District Abbreviations field is from the Contact object. The Appointment object has a master-detail relationship to the Contact Object and the Account Object. Is that what you were asking?
Steve MolisSteve Molis
Hi Lisa,

Sorry, but you need to bear in mind that I do not have any of these objects on my org, all that I've seen is a Report screenshot.  So I'm trying to wrap my head around this visualize how all of these records are (and aren't) connected.  

Have you done any testing in your Sandbox with The Power of One?  It's not going to break anything, it's literally just an inert field with a 1 in it on every record of that Object Type.  All you're doing in the report is counting up all of those 1's on each record to get the sum of unique records for each object 
Lisa PutnamLisa Putnam
I have not tried the Power of One yet. I wasn't sure it would work in this scenario. "Conference Relationship" is a picklist field so I need to count the number of times each of those picklist values is chosen. So how many records have "RE - Retired Elder" selected within that "District Abbreviation". If I put the Power of One on the Appointment object, how am I going to differentiate between the different conference relationships? It seems like it would just count the total number of records returned, independent of what the conference relationship is.
Steve MolisSteve Molis
Okay, I don't think it's gonna do what you're looking for.  If you have 10 Records the Power of 1 is gonna return the number 10, not the cound of each value in those 10 records.  For what you're describing you'd need to Group the Report by the Conference Relationship field 
Lisa PutnamLisa Putnam
Got it. I'm sure the Power of One will come in handy in a different situation. Thanks for your help!
Glen HuckinsGlen Huckins
How do I get multiple totals (like in your example) to display in a dashboard? 
Steve MolisSteve Molis
@Glen Huckins 
Can you post a screenshot of your source Report and the Dashboard Component settings you're using?
Glen HuckinsGlen Huckins
User-added imageUser-added imageUser-added imageUser-added image
Glen HuckinsGlen Huckins
@Steve Molis do these pictures give enough information? Thank you. 
Steve MolisSteve Molis

Glen Huckins 

Can you post a screenshot of your Report? 

What do you want to display in the Chart?
Glen HuckinsGlen Huckins
@Steve Molis 
I want the individual count of each check box. (i.e. Vendor at Fault = 5 , Shipping = 3 , ect. 
User-added image 
User-added image
 
Steve MolisSteve Molis
Okay that's not The Power of One, those are separate Checkbox fields.  

Are trying to display each selected value in a single Report Chart?
Glen HuckinsGlen Huckins
@Steve Molis yes. I would like to show the count for each total by "checkbox" checked in a single dashboard element.
Steve MolisSteve Molis
Okay, you won't be able to do that in a Doghnut, Funnel, or Pie Chart, the segements of those charts are the values in a single field, not multiple fields. 

And you've got way more than 4 Checkbox Fields, so that rules out a side-by-side bar chart too
 
Steve MolisSteve Molis
You could try creating a custom Formula(Text) field and use a Formula like this
 
SUBSTITUTE( 
IF(Checkbox_A__c = TRUE, "A, ", NULL) + 
IF(Checkbox_B__c = TRUE, "B, ", NULL) + 
IF(Checkbox_C__c = TRUE, "C, ", NULL) + 
IF(Checkbox_D__c = TRUE, "D", " NULL) + 
".", ", .", NULL)

 
Glen HuckinsGlen Huckins
@Steve Molis I used the above formula, but I get this as a result. 
User-added image 
I have this field already (I know multipicklist suck).
User-added image 
I originaly created the checkboxes to give a count of each selection so I could see how many each selection was used; using the formula you replied to a previous question of mine. 
User-added image 
So the above worked as far as giving me total count of each selction. 
User-added image 
I just need a way to display it in a Dashboasrd (the totals for all selections. Thank you either way for your time and assistance. 
 
Steve MolisSteve Molis
Yeah, that's the best you can do with 8 Checkbox fields
Glen HuckinsGlen Huckins
@Steve Molis thank you for the help. I appreciate the effort. 
Nishant Singh PanwarNishant Singh Panwar
Great Post @Steve Molis
Steve MolisSteve Molis
a few quick take-aways on the new Report "Unique Count" (beta) 

It only works at the Report Detail Row Level, it can't be used in a Report Summary Group (The Power of One can)

It cannot be referenced in a Report Summary Formula (The Power of One can) 

It only works in Lightning (The Power of One can be used in Lightning or Classic) 

So in the words of Winston Wolfe from Pulp Fiction...  
https://www.youtube.com/watch?v=8NlrgjgOHrw
John FowlerJohn Fowler
@Steve Molis 
Thanks for all your help on this thread and with the PoO (Tom too!). It's amazing! I've added it to all of my objects as I think it's the solution for a project I'm working on, but I'm stuck and can't figure it out.

I have daily usage reports coming into SFDC every day. Usage is measured by Controller. Some accounts have multiple controllers. If an account has multiple controllers, their usage is added together. For this, I can see the daily reports:

User-added image

For Reporting by Day, I can aggregate them together to get a total maximum usage for the day which is great.

User-added image
My issue comes when I want to report weekly, monthly, quarterly or yearly. I want to see the maximum aggregated daily controller number for the week, but If I choose 'sum' then it adds up all the capacity for all controllers all week, which isn't the number I'm looking for.

User-added image

User-added image
And if I choose 'max' it shows me the (1) maximum daily controller during that week, not the aggregated.

User-added image
I'm thinking once i can figure out weekly, i'll be able to do the rest. Is there a way for me to use the Power of One for this? Or is there something I'm just not seeing?

Any help is appreciated, this one is challenging me.

 
John FowlerJohn Fowler
Sorry, "SUM" graph is this one.
User-added image
 
Steve MolisSteve Molis
Sorry John Fowler, I'm having a hard time understanding what I'm looking at in your report.  Can you break it down into bite sized chunks?
John FowlerJohn Fowler
@Steve
I think I misunderstood bite size chunks and gave you full course meal. My apologies. Let me try again?

Custom object has fields (Report Date) and (Usage)

Report Date:            Usage
9/4                            Thing1 - 25
9/4                            Thing2 - 35
9/4                            Thing3 - 10

9/3                            Thing1 - 20
9/3                            Thing2 - 30
9/3                            Thing3 - 10

I’m looking for the best way to capture the max ‘daily usage’ (Thing1+Thing2+Thing3) on a weekly, monthly and quarterly basis.
Bonnie BaileyBonnie Bailey
Hello!  Jumping on here.  I have created a report using the power of one from Accounts.  It tallies up correctly in the report, but that tally is not the same in my my dashboard component:

User-added imageUser-added image
To back it up a bit, these are different Opportunity types.  I have 101 records of the types shown.  93 accounts have one or more of these Opp types.  

Here is how I've set up the component:

User-added image

What appears to be happening is that it is not looking at the Account tally, but rather it is still pulling in the types.  But why?

This is the chart I am using on the report (in order to use the Table component):

User-added image

I am using this report for two different components:  the one you see above, and another that counts the actual Opp types.  The tables look the same, but the one you see should have a lower total, as I've shown in the first image, ie one account having one or more type so number of accounts should be less that number of Opp types.  I originally had two separate reports and received the same data output in my dashboard components.

Any ideas?

Thank you!

BB  :)
Steve MolisSteve Molis
Hi Bonnie,
Could you post a screenshot of the Report reult that shows the Account Sum and the Record Count?   

Does that 101 match the Opportunity count? 
Steve MolisSteve Molis
@John Fowler 

Sorry it been a Hellacious week, I havn't had a chance to dive into it
Bonnie BaileyBonnie Bailey
Yes, the 101 matches the Opp count:

User-added image
Bonnie BaileyBonnie Bailey
Btw, would it be better if I moved my inquiry to new thread?
Steve MolisSteve Molis
Well we're alreadty here (and there's already about 200+ comments) 

Can you do me a favor and Add the Opportunity Po1 field to the Report and Sum it and add that to your DB Table 
Bonnie BaileyBonnie Bailey
Done.  Here is a snippet of what my report looks like:

User-added image
User-added image

I added that field to my component, but the results are the same:

User-added image
Steve MolisSteve Molis
Crap...  okay lemme see what I can do
Steve MolisSteve Molis
Tr7y adding them both to the Report and Table like this
1
User-added image
2
User-added image
Steve MolisSteve Molis
Oh wait!  I think I know what it's doing.  It's summing the count of Accounts in each Group, not the Total Number of Unique Accounts
Steve MolisSteve Molis
@Bonnie Bailey 
How many compoents does your Dashboard have? 
If you have a few slots to spare I have a quck hack that will work for you
Steve MolisSteve Molis
If you have a a slot for another component you can nest an Metric Component (with no Title or Header) under the Table Component (like this)
1
User-added image
2
User-added image
 
Bonnie BaileyBonnie Bailey
I have room in my dashboard.
Steve MolisSteve Molis
It's a "hack" but then again, so am I...
Bonnie BaileyBonnie Bailey
Here's what I did:

User-added image

So how did you get it so that your sum of accounts is less than your sum of opportunities?
Steve MolisSteve Molis
Well the Power of One returns the count of unique Accounts, but the problem with the Dashboard Table is that when you select Total it sums up the count of Accounts per each Program Type (that why I needed to add the Metric Component)
Bonnie BaileyBonnie Bailey
It really does make sense when I step back from it.  How can the account be split among the program types?  It can't.  Thank you so much!!  I think I owe you several cases of beer!  :)
Steve MolisSteve Molis
Which object is the Program Type field located on?  

if it's on the Opportunity, and there's more then 1 Opportunity per Account that would explain it
 
Steve MolisSteve Molis
I gotta jet soon and I'm gonna be off the grid until next week
Bonnie BaileyBonnie Bailey
The Program Type is on the Opportunity.  Yes, one Account may have one or more Opportunities.
Steve MolisSteve Molis
Then that explains why
Bonnie BaileyBonnie Bailey
Thank you again, Steve.  Enjoy your time off of the grid!  :)
Vincent AmmiratoVincent Ammirato

So is the "Count Unique Values in Report Results (Beta)" that's coming in the upcoming Winter release going to obviate this? And yes, I really want to know, I'm not just looking for a chance to use "obviate" in a legit sentence.

 

Thanks for everything, Steve. Been following you for awhile and I really value your advice and insights.

Steve MolisSteve Molis
I wouldn't do scheduling The Power of One for a dirt nap just yet. 

Count Unique Values only works at the Report Detail Row Level, it can't be used in a Report Summary Group (The Power of One can)

It cannot be referenced in a Report Summary Formula (The Power of One can) 

It only works in Lightning (The Power of One can be used in Lightning or Classic) 

So in the words of Winston Wolfe from Pulp Fiction...  
https://www.youtube.com/watch?v=8NlrgjgOHrw
Ankit GargAnkit Garg
Good Morning Steve,

Can I use the Power of 1 only on selected options?
Steve MolisSteve Molis
Ankit Garg

Can you elaborate and provide some context please?
Ankit GargAnkit Garg
Hi Steve,

Glad you are here to help :)

I would like the report to show clients that have not had a meeting logged in client interactions for the past four months. (Client Interaction is a custom object) I.e. The following have not been selected under ‘client interactions’ within the previous four months:
 
- Additional Value Meeting
- Account Management Meeting
- Renewal Meeting

My challenge is one account can have many opportunities and I want to check this condition in all the Opportunities associated with an account for last 4 months. So to say, I only want those accounts where no opportunites that is associated with an account have those 3 client interaction types.

I was trying to do with the power of 1 by summing up only these 3 client interaction types but still nop luck :( any alternatie method?

cheers
 
Steve MolisSteve Molis
Hi, you can't use The Power of One as a Report Filter like that.  I already posted a reply in your other post on this subject suggesting Cross-Filters
Ankit GargAnkit Garg
Thansk Steve :)
Scott PielstickerScott Pielsticker
Hi Steve. I can't get the example you give above to add up.

The “Grand Total 661 records” in the screenshot does *not* equal the eventual total of Users, Accounts, Opportunities and Opportunity Products shown. I would have expected it to.

Why doesn't it add up?

Thank you!
Steve MolisSteve Molis
The 661 is the count of "rows" if you export the Report Details to Excel or a CSV file, you'd get 661 rows (plus the column header). 
Ajeet SinghAjeet Singh
Thanks Steve