Problem:I recently faced a situation when I was creating a column chart, showing Monthly Visit Counts. As the VisitCount Values were quite small (maximum being 2) and since the axis has default setting for Max value and Increment as Auto, it started showing me decimal values or repeating the values (if you switch off the decimal) for the ranges on the Axis. Now, since I am displaying Counts that are supposed to be "integers", the decimals could be confusing to the end-user.
We have following dataset:
MonthName | VisitCount
We need to create a Column Chart with following:
MonthName will go on X-Axis
VisitCount will go on Y-Axis
Trick:Compute the Max value property of the Y Axis dynamically depending on the Max value in the dataset.
Step #1: Add Custom CodeHere we are assuming that we can have the Maximum value for the Axis to be the 10x factor of either 10, 25 or 50. That is 10, 25, 50, 100, 250, 500, 1000, 2500 and so on. If you want other ranges you can modify the varibale values and add more variables.
This code computes the Max value property to be set for the Axis Options, depending on the Maximum value of the Measure in the data set.
Public Function GetScaleMaxVal(ByVal DataMaxVal As Integer) As Integer
Dim ScaleMaxVal As Integer ScaleMaxVal = 10
Dim ScaleMaxVal10 As Integer
Dim ScaleMaxVal25 As Integer
Dim ScaleMaxVal50 As Integer
ScaleMaxVal10 = 10
ScaleMaxVal25 = 25
ScaleMaxVal50 = 50
While DataMaxVal > ScaleMaxVal
If DataMaxVal < ScaleMaxVal10 Then
ScaleMaxVal = ScaleMaxVal10
ElseIf DataMaxVal < ScaleMaxVal25 Then
ScaleMaxVal = ScaleMaxVal25
ScaleMaxVal = ScaleMaxVal50
ScaleMaxVal10 = ScaleMaxVal10 * 10
ScaleMaxVal25 = ScaleMaxVal25 * 10
ScaleMaxVal50 = ScaleMaxVal50 * 10End While
Step #2: Call the FunctionAs we are done writing the function, time to give it a call.
Go to your Chart
-> Vertical Axis properties
-> Axis Options
Click on Expression button (Fx ) and add following code:
Here we are using in built mathematical function Max along with our custom function.
Thats it and you are done!