Salesforce Certified Platform Administrator free question 8 / 10 · Domain 6: Data and Analytics Management
A report on opportunities is grouped by Lead Source. The administrator wants each Lead Source group row to show its amount total as a percentage of the amount total for the whole report. Which method matches Salesforce Help?
- Add a summary formula that uses PREVGROUPVAL() to divide each lead source's amount total by the previous group's total
- Add a row-level formula that divides each opportunity's amount by the report's total amount and then adds the results for each lead source
- Apply the Sum summary to the Amount column; the percentage of the grand total is shown along with each group subtotal
- Add a summary formula that divides the Lead Source group's amount total by the report's overall amount total
Show the answer and explanation
Answer: D
Why this is correct
Help on summary formulas divides report formulas into three kinds: summaries, summary formulas, and row-level formulas. A summary formula evaluates data at the specified group, at all summary levels, or at all summary levels and the grand total, and suits comparing a group's data with other groups or with the report total. One of its sample questions is the percentage of opportunities that come from each lead source.
Why the other options are wrong
"Add a row-level formula that divides each opportunity's amount by the report's total amount and then adds the results for each lead source" … A row-level formula is applied to each record in the report. Row-level formulas can't reference summary formulas, so you can't divide each row by the report's overall total in this way.
"Apply the Sum summary to the Amount column; the percentage of the grand total is shown along with each group subtotal" … Summaries are arithmetic functions of sum, average, minimum, and maximum applied to a number column; they produce subtotals for each group and a grand total for the report. A percentage of the total isn't one of those four.
"Add a summary formula that uses PREVGROUPVAL() to divide each lead source's amount total by the previous group's total" … PREVGROUPVAL() compares one group with another group. Comparing a child group with its parent group or the report total is what PARENTGROUPVAL() is for, and dividing by the previous group's total doesn't give a share of the whole.
Key point
Which of the three to use depends on what you apply it to. Sum, average, maximum, and minimum of a number column come from the Summarize option without writing a formula. A calculation per record, such as the number of days each opportunity took to close, is a row-level formula, and comparisons between groups or totals use a summary formula. In a report grouped by type and stage, you choose whether a summary formula applies to type only, stage only, the grand total only, or all of them. Creating formulas requires the Create and Customize Reports permission for private reports and the Report Builder permission for public ones.