Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
Based on the locations of the shifts
Where are these shifts defined?
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).
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
I did.
The table listed shows all values needed. The line graph shows that same data with three averages: from Jul-22 to Feb-23, from Mar-23 to Oct-23, and from Nov-23 to Mar-24. Where one average stops and the next starts isn't calculated, it's known from external sources. In this case, I know there is a change after Feb-23 and a change after Nov-23.
You could argue if that change was after February or after February plus March.
you could suggest the shifts after an absolute change over 40, or a positive change over 30 . or in many other ways. Are these shifts defined by you, the report designer, or are they defined by your report users dynamically?
The shifts are defined by the report designer, and do not dynamically change.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |