Author:Β Peggy Schael | Salesforce Trainer | WeLearnSalesforce
Itβs referred to as the greatest formula ever written. And it is so simple, youβll shake your head even more. BTW, it can actually be found on Trailhead when you dig deeper into formula fields. No, you canβt search for it. I tried, no luck. I stumbled over it when working my way through the Formulas and Validations Module. And bam, there it was.
HOWEVER! This module isnβt quite detailed enough to explain the full context. Therefore letβs talk about the problem first, the problem that led to this solution. You may not even know you had a problem! π€ Plus, thereβs one more feature you need to know about too, which likes to compete with itβ¦
The Reason the βPower of Oneβ Even Exists
Letβs take this Opportunity report as an example. This particular report contains a list of Opportunities which have been Closed Won. When you look at the total number of records it displays β20β. Since this is an Opportunity type of report the count refers to the Opportunity records. However, the report is grouped by Accounts and it may appear as if the total count refers to Account records. So, if youβre not sure, the first thing you can do is verify whatΒ type of reportΒ it is, such as Opportunity vs Account vs Cases etc. You can see the report type at the very top of the report where it says βReport: Opportunitiesβ.
Now, sticking to this report, what if you wanted to know the number of unique Accounts in this report too? Wellβ¦you wonβt get it. This example report is a rather small report, so you could easily do a manual count but thatβs not how we want to use Salesforce right? And just imagine youβve had a super long report with hundreds or thousands of records in it.
So what did I do at the time? Iβve twisted the report and recreated it in different ways, but I simply couldnβt get that unique count. I even looked for a solution on the AppExchange, but nothing straight forward.Β
Eventually, I stumbled over the βPower of Oneβ solution. ANDβ¦ Salesforce introduced a report feature not too long ago which likes to compete with the βPower of Oneβ but is not as good (yet). Read on to find out why!
Watch video tutorial including follow-along demo (βPower of Oneβ starts at 1:40min but I recommend watching the first part too):
The βPower of Oneβ Formula Solves Unique Record Counts in Reports
Letβs look at the βPower of Oneβ first before we discover the report feature solution. What we want to add is something like this:
The solution: Adding a custom formula field to the Account object called βUnique Accountsβ. But wait, arenβt we talking about the βPower of Oneβ? Thatβs right. We still want to name the field after what itβs meant to do. What it does behind the scenes is what we refer to the βPower of oneβ. So, what is it then?
πΆοΈ BTW, this content is part of our Salesforce Platform App Builder as well as Advanced Administrator Certification Courses:
How To Create the βPower of Oneβ Formula
The magic (or power!) lies in how you design this particular formula field. BTW, this is probably the most complicated formula ever, NOT!Β
This particular formula field merely contains the number 1. Thatβs it? Thatβs it!Β π€―Β Itβs a count of β1β against every single Account.
And now, all you need to do is to add the new field βUnique Accountsβ to the report as a column and youβre done! Youβre kidding? Nope!
And now? Do I need to create a βPower of Oneβ type of field for all additional unique counts I want to add to the report? NO! Luckily, Salesforce introduced a report feature a couple of releases ago which is called βUnique Countβ. Butβ¦
What About the Unique Count Report Feature?
Salesforce seem to have acknowledged the many headaches, since a lot of Admins still donβt know about the βPower of Oneβ, so they have added a new report feature right into the Report Builder. Phew!
All you need to do is go to the drop-down arrow at the top of the column and select βShow Unique Countβ. Butβ¦
πΆοΈ Thereβs one caveat:Β This feature only works for columns that areΒ not grouped. Youβll notice in the example above, the Account Name column is not grouped. If it was grouped like the first example report, this option does not even appear:
Combine βPower of Oneβ and βShow Unique Countβ in Reports
Because of the caveat I mentioned, follow these steps as your best practice:
- Create your report
- Add column and/or row groupings as needed
- Create a βPower of Oneβ formula for grouped columns/rows as required
- Use βShow Unique Countβ option on other columns which are not grouped
I hope you are as relieved as I was when I found out. Now tell me: Did you know this existed? Did you even know there was a problem? Iβd love to know.
WHAT ELSEβ¦
We make learning simple with our range of well-structuredΒ Salesforce Video Tutorials,Β downloadable Study Workbooksand realisticΒ Practice Exams.
And if you are brand new to the world of Salesforce, make sure to sign up to ourΒ FREE 21-Day Salesforce Beginners Challenge.