Salesforce Report: Bar Chart with Cumulative Line Chart

In Salesforce reporting (include Lightning), you can use a field only once, if you have admin right, you can create formulas field that returns the same value with the original one, so can use it too in the report.

We have a request to have a chart with opportunity amount in both monthly total and cumulative YTD. Salesforce report has the cumulative option for a line chart, but unfortunately, as mentioned in the first paragraph, we can't use a field more than once in a chart, while to create a formula field for this scenario of dynamic values seems like not possible. The easier option is to export the data and easily built it in Excel, but it would not be stored in the platform.

simple bar chart only shows the total monthly amount

change to line bar chart and enable the Cumulative option

add the new measurement, but only can select different field, in this case, I use Count, plot as line chart and as the second axis.

create new formula field Amount clone, but because the main chart is a bar chart, the Cumulative option is not available for the additional measurement.

Now, let's see how is the data looks like in the report for the last chart:

So, if we can get the cumulative value in the last column, instead of the same value, then we can achieve the report requested.
For row 1, it would be just that row itself.
For row 2, it would be row 2 amount + row 1 amount.
For row 3, it would be row 3 amount + row 2 amount + row 1 amount.

Remember, there is a function called PREVGROUPVAL() in the Custom Summary Formulas for the report. We can use this function for this need. This function calculates values relative to a peer grouping. If there’s no previous grouping, the function returns a null value.

This function returns the value of a specified previous grouping. A “previous” grouping is one that comes before the current grouping in the report. Choose the grouping level and increment. The increment is the number of columns or rows before the current summary. The default is 1; the maximum is 12. You can only use this function in custom summary formulas for reports.

This is the function pattern
PREVGROUPVAL(summary_field, grouping_level [, increment])
From here, see that you can adjust the incremental of previous grouping.

Adding Custom Summary Formula with PREVGROUPVAL() function allow us to get this table

Then we can easily add this chart to the report:

Step by step:
1. From your summary or matrix report, create Custom Summary Formula, in this example, I called it Cumulative Amount.

2. In "Where will this formula be displayed?", select "Grouping 1:".

3. Copy and paste following formula:
AMOUNT:SUM
+
IF(NOT ISBLANK(PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,1)),
PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,1), 0)
+
IF(NOT ISBLANK(PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,2)),
PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,2), 0)
+
IF(NOT ISBLANK(PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,3)),
PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,3), 0)
+
IF(NOT ISBLANK(PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,4)),
PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,4), 0)
+
IF(NOT ISBLANK(PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,5)),
PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,5), 0)
+
IF(NOT ISBLANK(PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,6)),
PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,6), 0)
+
IF(NOT ISBLANK(PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,7)),
PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,7), 0)
+
IF(NOT ISBLANK(PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,8)),
PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,8), 0)
+
IF(NOT ISBLANK(PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,9)),
PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,9), 0)
+
IF(NOT ISBLANK(PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,10)),
PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,10), 0)
+
IF(NOT ISBLANK(PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,11)),
PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,11), 0)

You can adjust the formula as necessary, change AMOUNT:SUM with RowCount if you need to show the number of records instead of Opportunity Amount.
Note: you can use this solution if your report has the maximum grouping of 13, because the maximum incremental for PREVGROUPVAL() is 12.

4. Add Vertical Bar Chart with Y-axis is the main amount, then add measure using Custom Summary Formula created in step 1-3, select Plot as Line Chart.

You also can enable "Plot on Second Axis" to get second Y-axis values for the line chart values.

Reference:

• PARENTGROUPVAL and PREVGROUPVAL
• Ultimate Salesforce Reports: bars with a cumulative line in one report chart