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

[derekli1700]

Hi, this is my sample datasheet where i have the monthly transactions of stores 1-1026 and customers A-IF. I'm trying to make a matrix table with stores as the first row, customer as second row and the sales etc as the values.

However, im trying to make it so that only the top and bottom 20 stores (based off sales) for first row and top 5 customers for second row are visible and could change depending on the time period of a slicer. 

I can't seem to wrap my head around the measures as the query seems to exceed resources due to the sheer amount of rows so would love an alternative suggestion. Thanks.

Formatting of matrix table below:

[v-vpabbu]

Hi @derekli1700,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[DataNinja777 · Accepted solution]

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,

[derekli1700]

Hi,

The stores seem to work well but is there a way to make the top 5 customers under each store show their respective sales? As it currently just says the same volume as the store. I'm assuming the relationships need to be changed but i'm playing around with it and nothing seems to match up.

[v-vpabbu]

Hi @derekli1700,

Ensure the relationships between the tables are correct so the context flows properly.

Regards,

Vinay Pabbu