Sum & Avg lines in Chart

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.

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.