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.

Name
Amt
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.

Snap 01.png

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.

Snap 02.png

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):

Snap 03.png

Value Axis 2:

Snap 04.png

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.

Snap 05.png

Snap 06.png

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.

Snap 07.png

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

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. M. van Foeken

    Hi Mahboob,

    Thanks for the great blog. I have a slightly different requirement and I wanted to check if you (or anyone else) knows how to resolve this.

    I also want to display an average as part of a line chart. So I have couple of customers with quarterly sales displayed in a line chart. Now I want to add an additional single line which displays the average across all customers.

    Adding a second measures results in the fact that the sales remain to be displayed per customer as customer is part of the assigned data.

    Is there simple solution to do this?

    With kind regards,

    Martijn

    (0) 
    1. Mahboob Mohammed Post author

      Hi Martijn,

      Thanks for stopping by and glad that you liked it.

      I assume the issue you’ve is with the Average formula, do you want to show average across all customers and quarters? Would you be able to manufacture some dummy data with result and/chart that you want to see?

      Thanks,

      Mahboob Mohammed

      (0) 

Leave a Reply