Forum Discussion

Nankaina's avatar
Nankaina
Helper I
2 years ago
Solved

Recalculating Average after a Point

Hi all,

 

I want to show a line graph with an average line that shifts about halfway through at a known point. Looking through the forums, I've seen ways to make moving averages, averages by year, or averages by a value in another column, but not based on before/after a single point.

 

For a concrete example, I made a table of dummy data using RandBetween with ranges of (100,150), (130,170), and (150,200) to represent three distinct averages. The table is shown below, with shifts happening in Mar-23 and Nov-23:

MonthValue
Jul-22138
Aug-22138
Sep-22148
Oct-22130
Nov-22141
Dec-22143
Jan-23150
Feb-23101
Mar-23135
Apr-23163
May-23158
Jun-23151
Jul-23145
Aug-23150
Sep-23142
Oct-23140
Nov-23185
Dec-23161
Jan-24166
Feb-24177
Mar-24172

 

Based on the locations of the shifts, the created graph should look something like this:

A normal average line would go above all the points on the left and below all the points on the right. This clearly shows three different averages and the months where they changed. The visual will be filterable, so any solution would need to work if some of the x-axis is hidden.

7 Replies

  • Based on the locations of the shifts

    Where are these shifts defined?

    • Nankaina's avatar
      Nankaina
      Helper I

      Apologies! These are manually chosen based on known changes in the data. They aren't recorded in the data currently (though I could easily add a column that denotes which months should start a new average, if that would help).

      • lbendlin's avatar
        lbendlin
        Super User

        Please provide sample data that fully covers your issue.
        Please show the expected outcome based on the sample data you provided.