The Power of One – The Greatest Formula Ever Written

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

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.