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

[derekli1700]

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 - i think i've gotten the top/bottom 20 sites locked down with my below measures but unsure how to get top 5 customers and not make the sales value for stores be only comprised of the top 5 customers sales, if that makes sense:

Total Volume = SUM(Table[Sales])

Rank by Volume = 
RANKX(
    ALLSELECTED(Table[Store]),
    [Total Volume],
    ,
    DESC,
    Dense
)

Set TopBottom Flag to: is Top/Bottom 20 for visual filter

[v-pagayam-msft]

Hi @derekli1700 ,
Has the issue been resolved on your end? If so, please share your solution and mark it as "Accept as Solution." This will assist others in the community who are dealing with similar problems and help them find a solution more quickly.

Thank you.

Regards,
Pallavi.

[v-pagayam-msft · Accepted solution]

Hi @derekli1700 ,
Thank you @mh2587 for the helpful response!

Upon my undesranding,I tried to recreate it on my local,so that I followed below steps:

• Create a maesure for Total volume using below(you already have this)
         Total Volume = SUM(SampleData[Sales])
• Create a Rank measure(which you already have)
  
  Rank by Volume =
  RANKX(
      ALLSELECTED(SampleData[Store]),
      [Total Volume],
      ,
      DESC,
      DENSE
  )
  
   
• Use a calculated column or measure to filter for only Top 20 and Bottom 20
  TopBottom Stores =

  IF(

      [Rank by Volume] <= 20 ||

      [Rank by Volume] >

          CALCULATE(MAXX(ALLSELECTED(SampleData[Store]), [Rank by Volume])) - 19,

      1,

      0

  )
•  
  Create a Customer rank caclulated column using below:
  Customer Rank Within Store =

  RANKX(

      FILTER(

          ALLSELECTED(SampleData[Customer]),

          SampleData[Store] = MAX(SampleData[Store])

      ),

      [Total Volume],

      ,

      DESC,

      DENSE

  )
• Make sure the matrix uses the [Total Volume] measure without any customer-level filtering.
  To do this, creat a separate measure for store-level volume

  Store Total Sales =

  CALCULATE(

      [Total Volume],

      REMOVEFILTERS(SampleData[Customer])

  )
  
  

  Configure the Matrix visual with Store and Customer as the rows. In the Values section, use a measure [Store Total Sales] that calculates the total sales for each store while ignoring the customer filter. Apply visual-level filters for Customer Rank within Store,set it for <=5 and Topbottom Stores = 1
  
  

  
  Please refer the screenshot and the attached file for your reference.

  

  
  If this solution meets your requirements,consider accepting it as solution.
  
  Thank you.
  
  Regards,
  Pallavi.

[mh2587]

check this video might help you
https://www.youtube.com/watch?v=ZAWEB6Kjn0Q

[derekli1700]

hey - i wrote in my original post just then how i dynamically filtered top and bottom 20 stores but unsure how to show the top 5 customers based off their sales. I dont think the video shows how to do the second row.
Here is my same sheet of where i'm up to with this step:
test.pbix