Hello,
For the sake of simplicity, I have a list of stores and sales (see table below). I would like to be able to select a store (via click, slicer, or preferrably drillthrough) and dynamically return a list of other stores with similar sales (for example, plus or minus 10%).
| Store_ID | Store_Name | Sales_Last_Year | Sales_plus10perc | Sales_minus10perc |
| 01 | Main Street | 150 | 165 | 135 |
| 02 | Elm Street | 140 | 154 | 126 |
| 03 | Center Street | 125 | 137.5 | 112.5 |
| 04 | Oak Street | 130 | 143 | 117 |
| 05 | Central Street | 175 | 192.5 | 157.5 |
| 06 | Broadway Street | 180 | 198 | 162 |
| 07 | Pine Street | 120 | 132 | 108 |
Is it possible to use DAX code to write a measure that returns a list of store names or store IDs within the +/- 10% range, based on the store selected using (a) a drillthrough (my preferred option), (b) a slicer list of stores, or (3) by clicking on it in the visual? If not a measure, can I create a calculated table that will change dynamically based on the selection and return the list of similar stores that way? If those don't work, is there another way to efficiently produce a list of observations within a similar range to a selection?
I know that I can create a "Between" slicer for sales and move the slicers to +10% and -10% of the value I want, and a table of stores will narrow to the stores I want. However, I'd like to do this quickly and build in more analysis based on the filtered list.
