The need to represent data visually using line chart showing cumulative values on top of a bar chart showing discrete values is almost universal. This can be easily verified by the amount of questions on the success community and the idea on ideaExchange that asks Salesforce to give users this ability. Check out:
https://success.salesforce.com/answers?id=9063A000000p9SrQAI
https://success.salesforce.com/answers?id=90630000000h2JkAAI – with all respect, here Steve Mo suggests it cannot be done (but it can be done in the report itself using formulas – stick with me)
https://success.salesforce.com/ideaView?id=08730000000jySzAAI – idea exchange
Salesforce by default gives an option to chart cumulative values as a line chart but it has the following limitations:
1. Not being able to combine it with bar charts
2. The additional value will also be cumulative
Sample image of what cumulitive chart editor looks like:
In this post, I will show you how to create a bar chart and a CUMULATIVE line chart on top of it in Salesforce:
Requirement: To create a chart that gives the total amount of leads created per year and a line showing cumulative number created. The following output is desired:
Solution:
1. Create a matrix report type (stick with me on why we chose matrix and not a summary)
2. Group by ‘created date’ and group dates by calendar year
3. Summarize by ‘one’ where one is the power of one formula. That is a formula of type number on Lead object which has a value of 1
The report builder should look like:
4. Add a bar chart (I prefer vertical over horizontal)
You should be able to see something like this:
…So far so good…
5. Now, we create a report formula (aka CSF) called “Cumulative number of leads” with the formula =
RowCount + IF(NOT ISBLANK(PREVGROUPVAL(RowCount, CREATED_DATE,1)), PREVGROUPVAL(RowCount, CREATED_DATE,1), 0) + IF(NOT ISBLANK(PREVGROUPVAL(RowCount, CREATED_DATE,2)), PREVGROUPVAL(RowCount, CREATED_DATE,2), 0)
Select the grouping as in the image below:
Immediately you will see that the formula is added to the grouping and your report builder looks like this:
Perfect?
As a validation that your formula worked, verify that thhe leads nubers are being accumulated as expected: 8+10=18 correct! and 8+10+2 = 30.. perfect!
6. Now to we need to add this to our chart. To do that click on edit chart. Under chart data tab, tick the combination charts checkbox. Display = line and Value = “Cumulative number of leads”. There you go! Our chart shows what we want:
7. Mission accomplished? Well almost there! Remember I asked you to stick around to know why we used matrix report even though we have grouped only by column. So this is where we change the “sum of one” label on the chart to something useful. Lets change it to “Number of leads”. Create a bucket field as follows:
8. Now drag this bucket field to ‘row grouping’ drop area and change you chart as in below image:
There you go!!!
We have achieved it and the final report looks like this:
Some considerations:
We used the formula only upto 3 grouping levels i.e. 3 years. You can add more levels into the formula to cover for more years.
Thank you for reading!
Cheers!
H Mann
December 19, 2016 at 10:53 pm
Hello! The only questions I would have is, how could I make this work to show month to month for one calendar year?
LikeLike
December 19, 2016 at 10:57 pm
Hello Michael,
You will need to do 3 things:
1. Apply a report filter on the year
2. Change the date grouping to Month
3. Extend the CSF up to 11 priorvalues
Thanks for reading through.
LikeLike
December 20, 2016 at 2:48 am
Worked perfectly! Thank you!
LikeLike
December 20, 2016 at 9:25 am
Glad it did. Thanks for posting the question that led me to creating the solution.
LikeLike
December 22, 2016 at 11:06 pm
Hello again, Sorry to be so needy, but how would I make a chart like this with Opportunity totals? Bar chart showing Opportunity amount per day, and a line of cumulative sales, with a close date of ‘Next 30 Days’? I’m not very bright when it comes to coding, and I have tried fooling around with what you have so graciously provided for this chart, to no avail. Let me know if you have the time! Thank you again so much and Happy Holidays!
LikeLike
December 23, 2016 at 10:14 am
Hi Micahel,
Use an opportunity report and in this case instead of summarizing by ‘one’, you summarize by the SUM of opportunity amount (you drag the oppt amount in the report and click Sum in the popup).
For managing the close date to be next 30 days, you may use the default ‘Date Field’ filter on the report. You set the Close Date and Range = Next 30 days.
Further the formula field would change as follows:
AMOUNT:SUM
+
IF(NOT ISBLANK(PREVGROUPVAL(AMOUNT:SUM, CREATED_DATE,1)),
PREVGROUPVAL(AMOUNT:SUM, CREATED_DATE,1), 0)
+
IF(NOT ISBLANK(PREVGROUPVAL(AMOUNT:SUM, CREATED_DATE,2)),
PREVGROUPVAL(AMOUNT:SUM, CREATED_DATE,2), 0)
…..
…..<this will go upto 30 times because you are looking at 30 days)
I know it can be a pain understanding reports sometimes, but try your best. You will eventually get it right.
OR If you like shortcuts:
I created the report and added it in the following package. You can install the package into any organization: https://login.salesforce.com/packaging/installPackage.apexp?p0=04t90000000Y3Io
Note: If you are installing into a sandbox organization you must replace the initial portion of the URL with http://test.salesforce.com
(IMPO: In the package report, the formula is till 12 times, expand it till 30 times please. I was too lazy 🙂 )
LikeLike
August 13, 2017 at 3:24 am
Hi, very good post ! this is great.
Just one question: in the cumulative line chart limitations, what do you mean by “2. The additional value will also be cumulative”.
In a line chart, yes if I see a value of 100 in June, and if July has +10 I expect to see a value of 110 for July and if Sept. has +5 then in my line chart I expect to see the line going up again to a value of 115 for Sept. Then indeed the additional value (e.g. +10 or +5) is also cumulative, isn’t it ? Maybe it’s an english translation I don’t get. Could you please explain me what you mean ? Thank you.
LikeLike
February 7, 2018 at 8:44 am
have sent you a pm
LikeLike
January 10, 2018 at 8:24 pm
Hi, How can we add other field other than created date i.e. a custom field ?
As of now i cant find that field in summary field list
LikeLike
February 7, 2018 at 5:30 am
Hello,
Is it possible that the option “plot additional value” is not available in lightening ? don’t find a way to do it there, works on the classic, but can not be even see the chart in lightening,
Thanks !
LikeLike
February 7, 2018 at 8:44 am
actually. am not sure. Ive kept a good distance from lightning for all good reasons you are already aware of
LikeLike
February 7, 2018 at 8:57 am
Probably wise… When created in classic, I put the chart in a dashboard and the second curve do not show (cumulative), but it is there in the report. Any clue ?
LikeLike
February 8, 2018 at 7:43 pm
This is really useful! I have a request to create a chart comparing the opportunities amount and the target amount for each month of the year. When I use matrix report, and group the expected close date, I don’t get 12 months, because the expected close dates only fall in say 4 month ends. I have added 12 dummy opportunities as my monthly target sales so that I get 12 months’ data in the matrix report. Now how do I get a cumulative line chart comparing the target and expected volume? Thanks a lot for your help.
LikeLike
February 24, 2018 at 2:34 am
I am a TRUE NEWBIE. Please help by providing a SCRENSHOT (step by step) of Item 3 in Solution.
====================================================================
How do you do the following?
3) Summarize by “one” where one is the power of one formula.
That is a formula of type number on Lead object which has a value of 1
>> Do you mean create a new CUSTOM FIELD called “one”?.
The report builder should look like:
LikeLike
March 17, 2018 at 2:52 am
Hi Salesforce mann,
You TRULY ROCK !! Thank you for posting the SOLUTION. Thx for your willingness to help
Regards, Sabrina Wong here 3/16/2018
LikeLike