I'm trying to compare where a particular store's Count(CustomerID) fits within the average of a subset of stores.
In the table, i'd like to show how the Count(CustomerID) compares to the average +/- 5 of the store's ranked sales. So for example, if i'm looking at store 6, i'd want to calculate the % difference between Store 6 and the average for Stores 1 through 11. If store 7, stores 2-12; store 8, stores 3-13 and so on. What would be the expression to calculate the average across those stores based on the CurrYr_RankSales?
I'm assuming its Above() and Below(), but i'm at a loss on how to calculate it across the CurrYr_RankSales dimension.
| Store | CurrYr_RankSales | Count(CustomerID) |
| Store1 | 1 | 158 |
| Store2 | 2 | 91 |
| Store3 | 3 | 50 |
| Store4 | 4 | 282 |
| Store5 | 5 | 475 |
| Store6 | 6 | 202 |
| Store7 | 7 | 291 |
| Store8 | 8 | 8 |
| Store9 | 9 | 387 |
| Store10 | 10 | 76 |
| Store11 | 11 | 81 |
| Store12 | 12 | 43 |
| Store13 | 13 | 129 |
| Store14 | 14 | 194 |
| Store15 | 15 | 210 |
| Store16 | 16 | 364 |
| Store17 | 17 | 77 |
| Store18 | 18 | 188 |
| Store19 | 19 | 227 |
| Store20 | 20 | 446 |
| Store21 | 21 | 178 |
| Store22 | 22 | 221 |
| Store23 | 23 | 138 |
| Store24 | 24 | 105 |
