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.
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.
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.
That's really a nice one Mahboob!!!
Great Webi trick !
You've been added to Webi 4.x tricks : summary for a better visibility. Keep posting !
William
Nice trick Mahboob.
Ray Khan
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
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