In the attached on the first tab (V3), I'd like to do two types of ranking in the “Top Account” table in the bottom right and show these rankings in the tooltip of this table (red border around it).
(1) First is an overall rank of each account by total 2018 Account revenue (e.g. 4 out 26) -- regardless of any filters that might be applied.
(2) The second is to show a rank by “Mkt Segment Revenue” within the segment selected (e.g. 3 out 20). For instance, the top accounts may change based on what segment bar you select (segments can be used as a filter, they are in the container with the blue border ). Hypothetical Example: Account LL is 3 out of 20 within the segment "Cyan". The challenge here is that accounts can fall into multiple segments.
I want to keep the sort order as it currently is built in the top accounts table, just want to add these rankings within the tooltip.
In other words, the Top Accounts table is dynamic based on the segment selected (e.g. white, orange, amber, etc.), so the ranking of accounts will shift based on the segment. I've attached a dummy data workbook. It would be great to show the answers in the workbook and provide an explanation in a reply. Thanks.