Advanced Function
An Advanced Function in Salesforce is a category of sophisticated formula or analytical operation used in reports, dashboards, CRM Analytics, and Tableau Cloud that goes beyond basic arithmetic and aggregation.
Definition
An Advanced Function in Salesforce is a category of sophisticated formula or analytical operation used in reports, dashboards, CRM Analytics, and Tableau Cloud that goes beyond basic arithmetic and aggregation. Advanced Functions include windowing operations (running totals, moving averages), comparison functions (percent of total, percent difference between rows), summary-row references (PARENTGROUPVAL, PREVGROUPVAL), and statistical operations (standard deviation, regression, percentile).
The term covers two distinct surfaces in Salesforce. In the standard Reports & Dashboards module, Advanced Functions appear as summary formulas that operate across grouped data: percent of total within a grouping, year-over-year deltas, running balances. In CRM Analytics and Tableau, Advanced Functions are expressed in SAQL (Salesforce Analytics Query Language) or in Tableau's calculated-field syntax, supporting an even broader set of analytical operations like cohort analysis, custom percentile bands, and time-series decomposition. Mastering Advanced Functions is what separates a basic report builder from someone who can answer business-strategy questions with self-service analytics.
Where Advanced Functions live in the platform
Summary formulas in standard reports
Standard Salesforce reports support two kinds of formulas: row-level formulas (calculations on each row) and summary formulas (calculations across grouped data). Summary formulas are where the Advanced Functions begin. The most useful are PARENTGROUPVAL (reference a summary value from a parent grouping, used for "percent of total" calculations) and PREVGROUPVAL (reference a summary value from the previous grouping, used for trends and deltas). A summary formula like AMOUNT:SUM / PARENTGROUPVAL(AMOUNT:SUM, GRAND_SUMMARY) gives the percent of pipeline each opportunity stage represents. These two functions cover most of the analytical questions that come up in pipeline, revenue, and case management reporting.
Cross-block formulas and Joined Reports
Joined Reports take Advanced Functions further by enabling cross-block formulas: calculations that reference values from multiple report blocks at once. A typical example combines a Closed Won block with a Pipeline block on the same report, then writes a cross-block formula computing the conversion rate of pipeline to won. The syntax references blocks by name and includes their grouping context. Cross-block formulas only work inside Joined Reports, which are themselves a more advanced report format. Most orgs use Joined Reports sparingly because they have layout constraints, but the cross-block formula capability is a powerful answer for executive dashboards that need to combine related but separately filtered data.
Bucket fields and conditional grouping
Bucket fields let report builders group continuous data into named ranges (small, medium, large) or categorical data into custom labels (priority customers, standard customers) without changing the underlying records. Buckets work alongside summary formulas to express segmentation analyses: average deal size by customer tier, conversion rate by lead score band. The combination of buckets plus PARENTGROUPVAL is what gives report writers an answer to questions like "what percent of our revenue comes from the top tier of accounts" without needing a developer to create a custom field. Buckets are saved with the report and do not modify the underlying object data.
CRM Analytics SAQL functions
CRM Analytics uses SAQL (Salesforce Analytics Query Language) for advanced calculations beyond what the dashboard widgets express. SAQL supports windowing functions (rolling sums and averages over time), case statements with rich conditionals, statistical functions (stddev, median, percentile), and time-series operations (compare against last period, period-over-period growth). The Compare Table widget in CRM Analytics dashboards exposes many of these as point-and-click options, but for anything more complex than the widgets handle, builders drop into SAQL directly. The trade-off is steeper learning curve for more analytical power.
Einstein Discovery and predictive functions
Einstein Discovery sits above standard Advanced Functions and answers a different question: not "what is the running total" but "what predicts a successful outcome." A Discovery story takes a target metric (deal won, customer churned) plus historical record data and produces a model that highlights the most predictive variables. The output appears inside reports as predicted values and recommended actions, generated by Einstein Discovery functions that integrate with Tableau and CRM Analytics dashboards. Discovery is the most advanced of the analytical functions in the platform, and it bridges from descriptive analytics (what happened) into predictive analytics (what is likely to happen).
Formula fields with advanced syntax
Custom formula fields on objects support several advanced functions that report builders sometimes treat as part of the broader Advanced Functions toolkit. CASE expressions express conditional logic with multiple branches. PRIORVALUE returns the value of a field before the current transaction, useful in workflow rules. DISTANCE calculates the geographic distance between two locations stored in Geolocation fields. ISCHANGED, ISBLANK, and TEXT functions handle the common cases of "did this change" and type conversion. These are not analytical functions in the same way reports use them, but they are advanced relative to the basic IF and concatenation that most formula fields start with.
Common Advanced Function recipes
Five recipes recur across orgs. First: percent of grand total, written as MEASURE:SUM / PARENTGROUPVAL(MEASURE:SUM, GRAND_SUMMARY). Second: percent change between consecutive groupings, written as (CURRENT - PREVGROUPVAL(MEASURE:SUM, COLUMN_GROUPING)) / PREVGROUPVAL. Third: running total, expressed using PARENTGROUPVAL with the running-sum option in dashboards. Fourth: filtered count using CASE inside an aggregate function, such as SUM(CASE Status WHEN $t$Won$t$ THEN 1 ELSE 0 END). Fifth: ranking, expressed with the RANK or DENSE_RANK SAQL functions in CRM Analytics. Memorize these five and you have answers to most "advanced reporting" requests that come in from the business.
When to upgrade from reports to CRM Analytics
Standard summary formulas in reports handle most descriptive-analytics use cases the business asks for. When the analytical questions get harder, three signals say it is time to upgrade to CRM Analytics: the report needs three or more chained summary formulas that no single formula expresses; the report runs slowly because it spans millions of records and triggers timeout errors; or the question requires time-series operations that PARENTGROUPVAL and PREVGROUPVAL cannot answer. CRM Analytics handles each of these well through SAQL, faster columnar data storage, and built-in time-series functions. The trade-off is a steeper learning curve and a separate license. Most enterprise orgs settle into a hybrid model: standard reports for tactical, day-to-day analytics; CRM Analytics dashboards for the executive-level questions that require advanced functions, deeper datasets, and richer interactivity. Knowing when to switch is one of the better instincts a senior analytics owner can develop.
Build a report with Advanced Functions
Adding Advanced Functions to a report is a sequence of small steps: start with the basic report, add the right groupings, then layer summary formulas on top. The walkthrough below builds a pipeline report showing percent of pipeline by stage with a running cumulative total.
- Build the base report and group by Stage
From the Reports tab, create a new report on Opportunities. Add the standard fields (Opportunity Name, Amount, Close Date, Stage). Group by Stage. Show the Amount field as a summary with the Sum aggregate. The result is a grouped report with subtotals per stage. Save the report and confirm the totals match what you expect before adding any formulas. Getting the base report right is half the work; the formulas are the easier half once the groupings and filters are correct.
- Add a percent-of-total summary formula
Open the report builder, click Add Summary Formula in the Fields panel, and define a new formula. Name it Percent of Pipeline. Set the format to Percent with 1 decimal place. Set the formula to AMOUNT:SUM / PARENTGROUPVAL(AMOUNT:SUM, GRAND_SUMMARY). Choose to display it at the Grand Summary level so it appears once per stage row. Save and run the report. Each stage now shows its percentage of total pipeline alongside the absolute amount.
- Add a running-total column via dashboard widget
Save the report, then open a dashboard. Drag the report onto the dashboard as a Lightning Table widget. In the widget options, find the Running Total toggle for the Amount column and turn it on. The dashboard now displays the cumulative pipeline as you read down the stages, which is exactly what an executive wants to see for a stage-funnel review. This is the example of how dashboard widgets expose Advanced Functions that the report itself cannot directly produce.
- Test edge cases and document the formulas
Run the report against different filter contexts to confirm the formulas behave correctly when the pipeline is empty, when only one stage has records, and when grand totals are zero. Test with non-admin profile users to confirm field-level security does not break the formula. Add a description to the report explaining what each summary formula computes and when to use it. The description appears in the Reports list view and helps future users understand whether the report is right for their question.
- PARENTGROUPVAL requires a grouping in the report. Calling it on an ungrouped report returns an error. Add a grouping or use the GRAND_SUMMARY parameter.
- Summary formulas cannot reference other summary formulas in the same report. Chain calculations into a single formula or use a custom formula field on the object.
- Cross-block formulas only work in Joined Reports. The same syntax in a standard report produces an error.
- Running totals are dashboard-widget features, not report features. Saving the report alone does not preserve the running total; the widget configuration on the dashboard does.
- SAQL functions in CRM Analytics are case-sensitive. Misspelling stddev as STDDEV or stdDev produces a function-not-found error.
Trust & references
Cross-checked against the following references.
- Summary Formulas in ReportsSalesforce Help
- Advanced Formula ExamplesSalesforce Help
Straight from the source - Salesforce's reference material on Advanced Function.
- Summary FormulasSalesforce Help
- SAQL OverviewSalesforce Help
- Joined ReportsSalesforce Help
Hands-on resources to go deeper on Advanced Function.
About the Author
Dipojjal Chakrabarti is a B2C Solution Architect with 29 Salesforce certifications and over 13 years in the Salesforce ecosystem. He runs salesforcedictionary.com to help admins, developers, architects, and cert/interview candidates sharpen their fundamentals. More about Dipojjal.
Test your knowledge
Q1. How can Advanced Function help improve sales performance?
Q2. What is the primary purpose of Advanced Function in Salesforce?
Q3. Who benefits most from Advanced Function in an organization?
Discussion
Loading discussion…