The Basics of Using PARENTGROUPVAL

This post covers the basics of using the PARENTGROUPVAL summary formulas in reports, and how the requirements change in matrix reports.

In my current admin role, it’s not in my job description to do reporting, but somehow I always find myself embroiled in emergency requests to draft reports for executives at my company. Maybe it’s just me, but I’ve always found the PREVGROUPVAL and PARENTGROUPVAL functions in Salesforce to be far more confusing than almost anything else. I think this comes from the weirdly sparse documentation around these two functions as the fact that their behavior is so wildly different from anything else we’re used to.

When we look at Salesforce’s documentation for these functions, it’s… rough… They give you a high-level explanation of how the functions work, but don’t give you much in the way of how to use them beyond a simple example:


In addition to this, they allude that there is a 3rd grouping level that has to be applied on matrix reports, but then don’t tell you much about what’s actually supposed to go there. So let’s try to answer that question and break down some examples. For this to make sense, we need to try to understand how summary formulas work, since that’s how we get our column groupings. Let’s do that first!

Basic Summary Formula (Count Records in a Group)

First, let’s start with a very basic Opportunity Report, with a configuration like this:

Report Builder Sidebar (Outline tab) from a Salesforce Opportunity report, with 'Account Name' field in the Group Rows section and 'Opportunity Owner', 'Opportunity Name', 'Stage', 'Amount', and 'Fiscal Period' in the Columns section
Group Rows: Account Name
Columns: Opportunity Owner, Opportunity Name, Stage, Amount, Fiscal Period

Then we’ll create a Summary Formula.

Click the Caret next to Columns, then click Add Summary Formula

Selecting the 'Add Summary Formula' option from the caret menu in the Columns section of the Report Builder sidebar on the Outline tab

Next, let’s name our column Row Count, set the output type to Number, and Decimal Points to 0.

For the Formula, just put RowCount, then Click Apply.

Column Name: 'Opportunity Count'
Formula Output Type: 'Number'
Decimal Points: '0'
Formula (General tab): 'RowCount'

That will give you a page that looks something like this:

Report preview showing the total number of rows at each grouping level (Account Name), as well as at the grand total level

You’ll now have your new Opportunity Count column, and on the Subtotal Rows for Account Name (our group column), you’ll have the number of records within each grouping, as well as the total number of records in the report in the Total Row (or Grand Total)

This takes us to an important facet of summary formulas. You can choose which groups they apply to, as well as whether it should be calculated for the Grand Total. This becomes important once we try to do something like calculate the percentage of Opportunities held by each Account. In fact… let’s do that!

Basic PARENTGROUPVAL Summary Formula (% Records in Group vs Total, Summary Report)

We’ll add a new summary formula that looks like this:

Column Name: '% Records'
Formula Output Type: 'Percent'
Decimal Points: '0'
Formula (General tab): 'RowCount / PARENTGROUPVAL(RowCount, GRAND_SUMMARY)'

We’re setting the Column Name to % Records, a Formula Output Type of Percent, and 0 Decimal Points.

For the Formula, we’re going to use:


This is taking the total count of the rows in each group, and dividing it by the row count at the Grand Total level.

When you try to hit save, you’ll probably see this warning now:

You must select a grouping context to use any report summary function.

To fix this, go into the Display tab, set the formula to apply to Specific Groups, and then in this case, Row Group should automatically be set to Account Name

On the Display tab
Where should this formula be applied?: 'Specific Groups'
Row Group: 'Account Name'

Now we can click apply.

Report preview showing the percent of all opportunities that each group (Account Name) represents

Looking back, that requirement kind of makes sense. If we tried to do this calculation at the Grand total level, we’d get 100%, so it wouldn’t tell us much. Let’s make this more difficult though and turn this into a matrix report to really show off how these summary formulas work, as well as PARENTGROUPVAL.

Basic PARENTGROUPVAL Summary Formula (% Records in Group vs Total, Double-Summary Rows Report)

So what happens when we use two different grouping levels? Now we have to chose which groupings our column will apply to. To test this out, let’s move Fiscal Period into the Group Rows under Account Name.

Move the 'Fiscal Period' field into the Group Rows section of the Report Builder Sidebar, under 'Account Name'

That gives us a report that looks something like this:

Report preview showing records now grouped first by 'Account Name', then by 'Fiscal Quarter'

Notice that the summary formulas still apply! Let’s re-open % Records and go back to the Display tab.

We now have two options for our Row Group— one for each of our group rows.

On the Display tab, we can configure which grouping we want to apply the formula to ('Account Name' or 'Fiscal Period')

Let’s take a look at both to compare them:

You can see above how our percentage calculation changes depending on which group we select. You’ll also notice that Opportunity Count doesn’t seem to care which is selected because we previously selected All Summary Levels for that summary field.

Now that we can see how those summary levels work, let’s go bigger and apply this to a matrix report.

Basic PARENTGROUPVAL Summary Formula (% Records in Group vs Total, Matrix Report)

If you’re still following along from above, let’s turn this into a matrix report by moving Fiscal Period down into the Group Columns.

Move 'Fiscal Period' field down from Group Rows into Group Columns

When you try to make this change, you’ll get an error like this:

Salesforce Error Message, saying:
Incorrect number of parameters for function 'PARENTGROUPVAL()'. Expected 3, received 2
Incorrect number of parameters for function ‘PARENTGROUPVAL()’. Expected 3, received 2

This is because your PARENTGROUPVAL function now requires you to also choose which column grouping it should use. This is unfortunately not well documented. The documentation linked above shows that it’s needed, but doesn’t tell you what goes there. Let’s fix that:

Back in the summary formula editor, we must add a 'Column Grand Summary':

First, we need to update the PARENTGROUPVAL formula to use ROW_GRAND_SUMMARY and COLUMN_GRAND_SUMMARY (These are not in the Salesforce documentation at time of writing):


Next, we need to go into the Display tab and set our Row Group and Column Group. We’ll set Row Group to Account Name and Column Group to Fiscal Period

On the Display tab, we can now apply 'Account Name' as the Row Group and 'Fiscal Period' as the Column Group

Now let’s hit Apply.

Report preview now showing that '% Records' now sums across all grouping combinations to 100%, and splitting the Row Group (Account Name) by each Column Group (Fiscal Period), and displaying the '% Records' for each subgrouping
Row Group: Account Name, Column Group: Fiscal Period (% Records Per Account Per Quarter)

This gave us a breakdown of the percentage of opportunities within each account, per each quarter.

Let’s look at the other options:

As you can see, the double-Grand Total option isn’t particularly useful, but the other options, which summarize by Account, by Fiscal Quarter, and by both can provide a valuable breakdown of our Opportunity data.

Those are the basics on how summary formulas and PARENTGROUPVAL work. In another post, we’ll break open some more of the possibilities with the function, complete with more examples and use-cases!

In the meantime, try it out yourself with this exercise on Trailhead!:


or for an extra challenge, take on the Lightning Experience Reports & Dashboards Specialist Superbadge.

6 thoughts on “The Basics of Using PARENTGROUPVAL”

  1. Thanks so much for this, it’s wild that Salesforce don’t explain all of the potential options for a particular function (even more than 2 years after you wrote this!)

    For some reason I had to specify the field name for the row group in a Matrix report, so my summary formula looks like:

    RowCount / PARENTGROUPVAL(RowCount, Contact.Year_of_Graduation__c, COLUMN_GRAND_SUMMARY)

  2. What if I have a report that is already giving me the sum of opportunities for accounts with columns by year (2021 & 2022). I’d like to see the percent change with an additional column. Is there a good solution here that I could use?

  3. Thank you! This is the best explanation of using the PARENTGROUPVAL function that I have come across yet. Can you use this function to calculate the percentage of each row value to the row grand total?

    1. If you’re looking to capture the percentage of total for a given group, that’s fairly straightforward. For example I can use this formula in a summary formula in the same report as above to get the percentage of total sales amount for a given grouping:

      If we’re trying to get the percentage for each row, it’s strangely more difficult since row-level formulas don’t let us use PARENTGROUPVAL in our formulas.

      The simplest way that we can get there is to group by the Opportunity Name (assuming that you name them uniquely in your org)…

      … and then you can use the same summary formula and just change the row group to Opportunity Name on the display tab of the Row Summary Formula editor.

      Hope that helps!

Leave a Reply

Your email address will not be published. Required fields are marked *