Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I need to calculate a moving average over time, however the time is set as numerical iterations, not dates.
The average needs to be over the 2 iterations - for example, the current iteration being 5, the calculation needs to take the average cycle time of this iteration and previous iteration 4. Then I also need the average of iteration 4 and previous iteration 3, and so on and so forth moving back in time. I eventually want a line chart tracking this moving average as we move forward in iterations.
Some sample data:
Iteration | Cycle Time | id |
1 | 20 | cc-1 |
1 | 17 | cc-2 |
1 | 22 | cc-3 |
2 | 23 | cc-4 |
2 | 5 | cc-5 |
2 | 15 | cc-6 |
2 | 10 | cc-7 |
2 | 11 | cc-8 |
2 | 18 | cc-9 |
3 | 21 | cc-10 |
4 | 20 | cc-11 |
4 | 14 | cc-12 |
4 | 25 | cc-13 |
5 | 9 | cc-14 |
5 | 11 | cc-15 |
5 | 12 | cc-16 |
5 | 27 | cc-17 |
5 | 20 | cc-18 |
The resulting chart data should be similar to the below:
Iteration | Avg |
5 | 17.3 |
4 | 20.0 |
3 | 14.7 |
2 | 15.7 |
Can anyone assist? I have tried adapting a previous question of mine HERE but this code won't adapt to when I change sum to average.
Solved! Go to Solution.
Hi @aslee
Please try the following calculated measure but change Table4 to the name of your table.
Avg = FORMAT( CALCULATE( AVERAGE('Table4'[Cycle Time]), FILTER(ALL('Table4'), 'Table4'[Iteration]>=MAX(Table4[Iteration])-1 && 'Table4'[Iteration]<=MAX('Table4'[Iteration]) ) ), "##.0")
Hi @aslee
Please try the following calculated measure but change Table4 to the name of your table.
Avg = FORMAT( CALCULATE( AVERAGE('Table4'[Cycle Time]), FILTER(ALL('Table4'), 'Table4'[Iteration]>=MAX(Table4[Iteration])-1 && 'Table4'[Iteration]<=MAX('Table4'[Iteration]) ) ), "##.0")
Thank you @Phil_Seamark! This has worked and looks great in a table - but for some reason I get this error when I go to put it in a line chart
My x-axis is Iteration, which is set to numerical integer format.
Anyone have any ideas what this means?
Nevermind - worked it out! I took out the formatting section and it fixed it.
Thanks again!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |