Forum Discussion
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:
| Month | Value |
| Jul-22 | 138 |
| Aug-22 | 138 |
| Sep-22 | 148 |
| Oct-22 | 130 |
| Nov-22 | 141 |
| Dec-22 | 143 |
| Jan-23 | 150 |
| Feb-23 | 101 |
| Mar-23 | 135 |
| Apr-23 | 163 |
| May-23 | 158 |
| Jun-23 | 151 |
| Jul-23 | 145 |
| Aug-23 | 150 |
| Sep-23 | 142 |
| Oct-23 | 140 |
| Nov-23 | 185 |
| Dec-23 | 161 |
| Jan-24 | 166 |
| Feb-24 | 177 |
| Mar-24 | 172 |
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
- lbendlinSuper User
Based on the locations of the shiftsWhere are these shifts defined?
- NankainaHelper 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).
- lbendlinSuper User
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.