Dynamic Chart Axis Scaling in Paginated Reports

The Challenge

Although I prefer to always deliver a Power BI report, I still occasionally deliver paginated reports as a solution when it satisfies requirements well. In one reporting solution, I was asked to deliver a sales breakdown report with almost 100 trend charts, mostly laid out like small multiples. In my use case, it made the most sense for each Y-axis minimum, maximum, and interval to be set independently on each chart because each chart’s sales volume is not being compared to another chart’s sales volume. The end users of this report know to read the vertical axis values to interpret each chart’s scale. I wanted each chart to feel consistently readable even though scales between charts would vary greatly. I needed to find a solution that would calculate the minimum, maximum, and interval properties for me. What a daunting task it would be to manually manage all of those axes as data changes over time!

The Solution

My first thought was to leverage expressions on the minimum and maximum properties on the Y-axis of each chart. Once I dialed the expressions in, I could establish a pattern to copy / paste to the other charts. I didn’t even make it through a dozen charts before the tediousness wore me down. The charts in my report varied enough that I needed to add headroom to some charts but not all of them. Others needed a little padding at the bottom. The expressions worked, but I quickly found that my initial approach was more hands on than I wanted. There had to be a better way!

And there is! With the help of a colleague, and brainstorming with Claude, a solution arose that alleviates my challenge quite well. Report Builder allows developers to add custom Visual Basic (VB) code to build reusable functions for use within a report. The functions handle all of the heavy lifting consistently simplifying the expressions needed on each chart. Claude helped us dial in the code and how to implement it. I have tested the code successfully across multiple reports. Here’s the code:

Public Function GetNiceMajorUnit(ByVal dataRange As Double) As Double
    If dataRange = 0 Then
        Return 10
    End If
    Dim absRange As Double = Math.Abs(dataRange)
    Dim magnitude As Double = Math.Pow(10, Math.Floor(Math.Log10(absRange)))
    Dim normalized As Double = absRange / magnitude
    Select Case True
        Case normalized <= 1.5
            Return magnitude * 0.5
        Case normalized <= 3
            Return magnitude * 1
        Case normalized <= 7
            Return magnitude * 2
        Case Else
            Return magnitude * 5
    End Select
End Function
Public Function NiceAxisMin(ByVal minVal As Double, ByVal majorUnit As Double) As Double
    If majorUnit <= 0 Then
        Return minVal
    End If
    Return Math.Floor(minVal / majorUnit) * majorUnit
End Function
Public Function NiceAxisMax(ByVal maxVal As Double, ByVal majorUnit As Double) As Double
    If majorUnit <= 0 Then
        Return maxVal
    End If
    Dim axisMax As Double = Math.Ceiling(maxVal / majorUnit) * majorUnit
    Dim headroom As Double = majorUnit * 0.1
    If axisMax - maxVal <= headroom Then
        Return maxVal + headroom
    End If
    Return axisMax
End Function

What is this code doing?

This code contains three functions: GetNiceMajorUnit, NiceAxisMin, and NiceAxisMax.

GetNiceMajorUnit contains a single parameter which requires the data range being displayed within the chart – the Maximum – Minimum. The function first finds the order of magnitude for the supplied range. Then it normalizes the magnitude to a number between 1 and 10. Finally, it uses the normalized number to determine the 1-2-5-10 “nice number” multiplier. That “nice number” is what guarantees that the gridline spacing always looks intentional regardless of the data’s actual scale.

NiceAxisMin contains two parameters. The first requires the minimum value displayed on the chart and the second parameter is the major unit (i.e. the interval from above). The function takes the minimum value and rounds down to the closest major unit to set the baseline for the axis.

Finally, NiceAxisMax also contains two parameters. The first requires the maximum value displayed on the chart and the second parameter is the major unit (i.e. the interval from above). The function takes the maximum value and rounds it up to the closest major unit for the highest value on the axis. The function then also checks to see if the rounded up major unit value is really close to the maximum value on the chart. If so, a 10% pad is added for headroom.

Implementing the Code

Implementing the code is fairly simple:

1. Copy the code from the code cell above. Paste the code into Report Builder in the Code section of the Report Properties window. Report Properties are accessed in the Properties pane (if visible) or by right clicking in the gray area around the report page and selecting “Report Properties” from the popup menu. Once the code is pasted, click “OK”.

3. Configure the axis Minimum property. In the Axis Properties window, click the fx button to the right of Minimum to open the Expression window for the Minimum property. Enter the expression below, making sure to include the = (equal sign) at the beginning of the expression. Replace YOUR_FIELD with the name of the field that you are visualizing. On the second line, make sure to wrap the field in the Min() function. Lines 3-5 dynamically calculate the range of values for the interval.

=Code.NiceAxisMin(
  Min(Fields!YOUR_FIELD.Value),
    Code.GetNiceMajorUnit(
      Max(Fields!YOUR_FIELD.Value) - 
      Min(Fields!YOUR_FIELD.Value)
    )
)

4. Configure the axis Maximum property. In the Axis Properties window, click the fx button to the right of Maximum to open the Expression window for the Maximum property. Enter the expression below, making sure to include the = (equal sign) at the beginning of the expression. Replace YOUR_FIELD with the name of the field that you are visualizing. On the second line, make sure to wrap the field in the Max() function. Again, lines 3-5 dynamically calculate the range of values for the interval.

=Code.NiceAxisMax(
  Max(Fields!YOUR_FIELD.Value),
    Code.GetNiceMajorUnit(
      Max(Fields!YOUR_FIELD.Value) - 
      Min(Fields!YOUR_FIELD.Value)
    )
)

5. Finally, configure the axis Interval property. In the Axis Properties window, click the fx button to the right of Interval to open the Expression window for the Interval property. Enter the code below, making sure to include the = (equal sign) at the beginning of the expression. Replace YOUR_FIELD with the name of the field that you are visualizing.

=Code.GetNiceMajorUnit(
  Max(Fields!YOUR_FIELD.Value) -
  Min(Fields!YOUR_FIELDt.Value)
)

6. That’s it! Now run the report to test that the code and expressions are producing the expected results. If so, your chart will now dynamically calculate the axis minimum and maximum with nice, even intervals in between.

In some cases you may not want to dynamically calculate the interval but rather manually hardcode the interval instead. The code and expressions support both use cases. In the Minimum and Maximum expressions, simply remove the Code.GetNiceMajorUnit function call (lines 3-5) and enter the desired value. For the Interval property, there is no need to enter the expression. Instead, just enter the desired interval value.

In Conclusion

Before sending out paginated reports, I like to have a member of the team do a review to ensure quality and accuracy of the report. Not only have these dynamic, nice axes increased the consistency of the charts across reports, but it’s also saved the team time of manual adjustments as the data changes. And less manual maintenance has meant more time that the team can spend on bringing value.