I've tried using ChatGPT, asking everyone at the office and searching for existing posts here and on other sites and have not found anything that works yet.
I am working with a table that has a SHELF column and an AVG REVENUE column for every combination of a number of filters. So product A has a different AVG REVENUE associated with 6 SHELF levels (SHELF A, SHELF B, etc.), as does product B, and they change based on geography etc. etc.
All I'm looking to do, which seems really straightforward to me but maybe I'm underestimating because PowerBI refuses to let me do it, is return the SHELF value with the highest AVG REVENUE taking the page filters into account.
Example:
| PRODUCT (text column) | SHELF (text column) | AVG REVENUE (measure) |
| ABC | A | 8.2 |
| ABC | B | 4.1 |
| ABC | C | 9.0 |
| ABC | D | 10.3 |
ABC | E | 5.6 |
| ABC | F | 1.2 |
| XYZ | A | 7.8 |
| XYZ | B | 9.2 |
| XYZ | C | 4.4 |
| XYZ | D | 7.8 |
| XYZ | E | 5.5 |
| XYZ | F | 6.7 |
Card visulization with filter set to PRODUCT ABC that evaluates the MAX of AVG REVENUE for PRODUCT ABC and simply returns "SHELF D". If I were to change the filter to PRODUCT XYZ, the card should shift to say "SHELF B".
It seems I'm unable to return an index against a text based column, but all alternative recommendations I'm finding also will not work. I've been at this for over a week, please help!!!
