Salesforce CPQ Complex Summary Variables
How to use a summary field to multiply multiple records (with examples)

Roycon has been operating in the Salesforce CPQ world, delivering comprehensive solutions and adaptions to our clients that require a robust solution for their business needs. Salesforce CPQ is an absolute workhorse that can handle multiple pricebooks, quoting methods, markup and discount ability, contract/order auto creation, and much much more. We recently tackled an obstacle when helping a client transition from using a complex spreadsheet (with layered cell references and calculations) to using the Salesforce CPQ via Quote Configurator and the Quote Line Editor. The heavy lifting was done by utilizing Summary Variables which is what we’ll focus on here.

Summary Variables are extremely useful but lack certain abilities. I recently was attempting to multiply the sum of a field by a number then add another field to it (example 2 below) and was unable to find any resource. Hopefully this will provide a solution and maybe even inspiration for a creative solution.

What is a Summary Variable?

“Summarize number field information about products into a single object called a summary variable. You can reference a summary variable in price conditions, price actions, product rule error conditions, quote term conditions, and other summary variables.”

Summary variables shine when either referencing one field and multiplying/dividing/adding subtracting a value e.g.
Product A quantity field * 4
Product B quantity field + 1

Or a bit more complex – stacking single fields by using multiple summary variables
e.g. Product A quantity field + Product B quantity field + Product C quantity field.

But what happens when that list is more than a dozen? When that list is constantly evolving? A simple approach would require the “chained” Summary Variables to constantly be remade for new/discontinued Products.

What happens when you try to multiply a sum then add another number to it?
E.g. ((Product A quantity field + Product B quantity field + Product C) * 4) + Product D quantity field. The result of that formula would set the quantity for what we’ll call “Product E” when I am in the Salesforce CPQ Quote Line Editor (QLE).
The Summary Variable is not able to have a “stacked” sum AND multiply. It’s one or the other.

I needed a Summary Variable (and well as Product Rule and Price Rule for full implementation) to allow me to handle these more complex numbers. Here’s what I did that allowed both examples above to function with flexibility and complexity.

Example 1 – stacked Summary Variables (Product A quantity field + Product B quantity field + Product C quantity field+…)
I created a checkbox field on the Product object called “Uses Sum of A to Z” and added it to the page layout.
For Products A to Z (26) I navigated to the Product record and checked that box.
Then I created a Summary Variable as follows:
Target Object = Quote Line
Aggregate Function = Sum (to take the sum of what field is defined in Aggregate Field)
Aggregate Field = Quantity (to sum the Quantity field on the quote line)
Filter Field = Uses_Product_A_to_Z__c (the API name of the checkbox field. I added this manually by going to Setup->Summary Variable object->Filter Field field->adding API name to picklist)
Operator = equals
Filter Value = TRUE (to filter checked boxes)
This allows me to check boxes on new Products, and uncheck boxes on old discontinued Products. It doesn’t rely on Product Code or Product Family. It’s easy to filter, add to and allows the ability to multiply as shown in the next example.

Example 2 – ((Product A quantity field + Product B quantity field + Product C quantity field ) * 4) + Product D quantity field
I created a checkbox field on the Product object called “Uses Product E” and added it to the page layout.
For Product A,B, and C I navigated to the Product record and checked that box.
I then created a Summary Variable as follows:
Target Object = Quote Line
Aggregate Function = Sum (to take the sum of what field is defined in Aggregate Field)
Aggregate Field = Quantity (to sum the Quantity field on the quote line)
Filter Field = Uses_Product_E__c (the API name of the checkbox field. I added this manually by going to Setup->Summary Variable object->Filter Field field->adding API name to picklist)
Operator = equals
Filter Value = TRUE (to filter checked boxes)
Composite Operator = Multiply
Variable Element = null
Value Element = 4

This provided me the ability to Sum one field on multiple records and Multiply with just one Summary Variable. To complete the job I needed one more Summary Variable to add in Product D which gives us a final product of:
((Product A quantity field + Product B quantity field + Product C) * 4) + Product D quantity field

*Remember that the Summary Variable Filter Field is a picklist field and that you will manually need to navigate to it in setup and add the API name to the picklist.*

The approach to both these examples reduced the number of overall Summary Variables significantly. Less variables mean less chance of something going wrong – a typo, a wrongly selected Aggregate Function, a missed Operator, etc. In the future I’ll be able to modify these Summary Variables much quicker and easily than editing 20+ Summary Variables and creating who knows how many.

While this is simply scratching the surface of Salesforce CPQ, we hope this has clarified Summary Variables and given you an advantage with this complex and powerful ecosystem.

For more information about Salesforce CPQ or working with Summary Variable fields in CPQ, feel free to reach out to us here.

About Roycon
We’re an Austin-based Salesforce Consulting Partner, with a passion and belief that the Salesforce platform’s capabilities can help businesses run more efficiently and effectively. Whether you are just getting started with Salesforce or looking to realize its full potential, Roycon specializes in Salesforce Implementations, Salesforce Ongoing Support, and Salesforce Integrations, and Development. We’re the certified partner to guide the way to increase Salesforce Adoption, make strategic decisions, and build your Salesforce Roadmap for success.