How to dynamically show Top and Bottom rows in a Matrix Table

Hi derekli1700 ,

 

To dynamically display only the top and bottom 20 stores based on sales in the first row of a matrix and the top 5 customers within those stores in the second row, while keeping performance manageable, you should filter the matrix using RANKX logic inside virtual tables rather than forcing the matrix to process all combinations directly.

Start by creating a calculated table to isolate just the top and bottom 20 stores. This table can be created using the following DAX expression:

TopBottomStores =
VAR SalesPerStore =
    ADDCOLUMNS(
        SUMMARIZE('YourTable', 'YourTable'[Store]),
        "TotalSales", CALCULATE(SUM('YourTable'[Sales]))
    )
VAR TopStores =
    TOPN(20, SalesPerStore, [TotalSales], DESC)
VAR BottomStores =
    TOPN(20, SalesPerStore, [TotalSales], ASC)
RETURN
    UNION(TopStores, BottomStores)

Next, to limit the customers displayed per store to only the top 5, you can create a measure that evaluates whether a customer is among the top 5 customers for a given store. This logic relies on SELECTEDVALUE and ALLEXCEPT to rank customers within each store context:

ShowCustomer =
VAR CurrentStore = SELECTEDVALUE('YourTable'[Store])
VAR CurrentCustomer = SELECTEDVALUE('YourTable'[Customer])
VAR SalesPerCustomer =
    CALCULATETABLE(
        ADDCOLUMNS(
            VALUES('YourTable'[Customer]),
            "CustomerSales", CALCULATE(SUM('YourTable'[Sales]))
        ),
        ALLEXCEPT('YourTable', 'YourTable'[Store])
    )
VAR RankedTable =
    ADDCOLUMNS(
        SalesPerCustomer,
        "Rank", RANKX(SalesPerCustomer, [CustomerSales], , DESC)
    )
VAR CustomerRank =
    CALCULATE(
        MAXX(
            FILTER(RankedTable, [Customer] = CurrentCustomer),
            [Rank]
        )
    )
RETURN
    IF(CustomerRank <= 5, 1, 0)

You can then use the ‘Store’ field from the TopBottomStores table as your matrix row and apply a visual-level filter for [ShowCustomer] = 1 to restrict the customers displayed. This approach prevents generating the full store-customer combination set in memory and keeps things responsive under time-based slicers.

 

Best regards,