
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Above() Below() against a dimension
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 |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @jasonrimkus, I suppose you need a combination of Above and RangeAvg:
RangeAvg(Above(Count(CustomerID), 5, 12))
JG
