How to dynamically show Top and Bottom N in Matrix Table for two rows

[james1700]

Hi, This is my sample sheet im using. Screenshot of example:

I'm struggling to have my matrix table simultaneously and dynamically show the top and bottom 20 stores and their top 5 customers (based off Sales). The format would be Stores and Customers as the first two rows and the Sales as the Value.

Basically if i select a date like Jan - March 2025, it would show the top and bottom 20 stores (40 rows) in the table and if i click on any of the sites, it would show their top 5 customers. (and the sales of the stores will be the overall store volume for that period - not exclusively the top 5 customer's sales)

Would really appreciate this help - have been looking through youtube tutorials and forums on dynamic N measures but none of them seem to account for time/another top n row.

 

[AliGW]

Where would you be selecting the date? Where should the results appear? Please manually mock up an example.

if i click on any of the sites, it would show their top 5 customers.

This sort of 'hot clicking' isn't possible in Excel, but you could have some sort of dependent drop-dwon list instead.

 

[Debaser]

If you want Top and Bottom N ranked, you can create two ranking measures - one in ascending order and the other descending, then just take the minimum of the two as another measure and filter that for <= 20.