Hi All,
Bit of an odd ask, but I'm looking for some DAX help that takes what a measure (at lower grain) has calculated, and calculates an average over these results, at a grouped level.
Here's some data to illustrate:
| Year Week | Year Month | Plan | Actual | ABS Difference | Weekly Accuracy |
| 202101 | 202101 | 28 | 64 | 36 | -28.6% |
| 202102 | 202101 | 12 | 22 | 10 | 16.7% |
| 202103 | 202101 | 83 | 83 | 0 | 100.0% |
| 202104 | 202101 | 55 | 65 | 10 | 81.8% |
| 202105 | 202102 | 30 | 71 | 41 | -36.7% |
| 202106 | 202102 | 7 | 52 | 45 | -542.9% |
| 202107 | 202102 | 72 | 13 | 59 | 18.1% |
| 202108 | 202102 | 82 | 82 | 0 | 100.0% |
| 202109 | 202103 | 37 | 1 | 36 | 2.7% |
| 202110 | 202103 | 92 | 20 | 72 | 21.7% |
| 202111 | 202103 | 43 | 40 | 3 | 93.0% |
| 202112 | 202103 | 34 | 29 | 5 | 85.3% |
| 202113 | 202103 | 32 | 85 | 53 | -65.6% |
Where 'Weekly Accuracy' is: =1-(ABS Difference/Plan)
Desired result:
| Year Month | Monthly Accuracy (Average) |
| 202101 | 42.48% |
| 202102 | -115.37% |
| 202103 | 27.43% |
Where 'Monthly Accuracy (Average)' is: Average of the weekly accuracy results grouped at Year Month level.
Hope this makes sense and someone is able to help.
Cheers,
Matty
