Forum Discussion

derekli1700's avatar
derekli1700
Icon for Helper III rankHelper III
1 year ago
Solved

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

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

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

       

4 Replies

    • derekli1700's avatar
      derekli1700
      Icon for Helper III rankHelper III
      1 year ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.