Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
Showing results for 
Search instead for 
Did you mean: 
Active Contributor

Often there is a requirement to show Sum & Avg lines in Charts, and people try different workarounds to address this issue/requirment. One common way is create multiple charts on top of each other, formatting the additional charts to make everything (font/background) white and/or transparent except that one line used to show the Sum or Avg. I think that's a maintenance nightmare for you and/or other developers if and when they takeover the maintaining tasks of that report. I'll show you what I do in such situations, I'll be using the below sample data in this exercise.


A001 456
A002 356
A003 432
A004 763
A005 653
A006 563
A007 234
A008 943
A009 234
A010 492
A011 7,542
A012 146
A013 254
A014 332
A015 347
Sum: 13,747
Average: 916.47

Step 1
: Create Avg variable using formula =Average([Amt]) in Block and Sum variable using formula =Sum([Amt]) in Block, these 2 variables will be used to show 2 straight lines, one for Sum and the other for Avg. You may need to create special formulas for Sum and Avg if and when you've issues with this formula.

Step 2: Insert a Combined Column Line Chart with 2 Y-Axes and assign data in Category Axis, Value Axis 1 & Value Axis 2  as shown below and click OK.

Step 3: The chart looks pretty good, except that the maximum value on scale on the 2 Value axes (Y Axis) is not the same (10,000 and 20,000 in this case) which doesn't look good, especially when we want to show lines for Sum & Avg values related to all the Amt values in the Chart.

We want the maximum scale value on Y-axis to be the same for both value axes, and as close as possible to the Sum([Amt]) which is 13,747 (in this case) per the sample data in table on top.

Let's create a variable Max value on Y axis using the formula =Round([Sum]+1000;-3). What this formula does is add a 1000 to 13,747 (the Sum([Amt])) which equals to 14,747 and then round that value to the next closest thousand, which is 15,000. That's cool, isnt it? Now, we can use this variable as the Fixed value for Maximum value on Y axis on both value axes in the Chart. Go to Format Chart -> and update the Value Axis and Value Axis 2 as shown below and click Apply & OK.

Value Axis (meaning, Value Axis 1):

Value Axis 2:

Step 4: Voila, we see that the Chart looks much better now, max values on both the value axes scales is 15,000 and value is dynamic, how cool is that? Let's remove an extra scale (from right). Update the Value Axis 2, remove the check mark from check box from General -> Value Axis 2.

Step 5: You can update format further if and as you need. I formatted the Data series to show Sum with green color line and Avg with orange color line as below.

That's it. Enjoy!!! Please feel free to comment.

Labels in this area