How to dynamically show Top/Bottom 20 Rows based off Values and Time slicer in Matrix Table

[derekli1700]

Hi, This is my sample sheet im using and this is the pbix file i have:

Basically i have a mastersheet of stores and monthly sales transactions by customers.

I want my Matrix Table to show only Top and Bottom 20 Site's sales and their top 5 customers based off the period Slicer. E.g) i select 2024 Jan to March and it would show me the top and bottom sites for 2024 Jan to March and only the top 5 customers for that time period. The Sales value should only show the store's total sales and not only just the top 5 customer's sales. 

Currently i show the top 5 customers as below and set to 1 as the matrix table measure:

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

 I'm trying to filter top and bottom 20 customers with this table BUT it sometimes excludes stores when i pick a time period so some of the top and bottom 20 stores are not included and the table doesnt = 40 rows

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

I'm trying to show the sales volume through this measure where it show's the depicted store's overall volume and if you drill down to customers, it shows their volume as well

MEASURE-Volume = 
VAR IsCustomerLevel = ISINSCOPE('test(Sheet1)'[Customer])
VAR CurrentCustomer = SELECTEDVALUE('test(Sheet1)'[Customer])
VAR CurrentStore = SELECTEDVALUE('test(Sheet1)'[Store])

-- Calculate store total (for when not at customer level)
VAR StoreTotal =
    CALCULATE(SUM('test(Sheet1)'[Sales]), REMOVEFILTERS('test(Sheet1)'[Customer]))

-- Build ranked customer table (only in customer context)
VAR CustomerSalesTable =
    CALCULATETABLE(
        ADDCOLUMNS(
            VALUES('test(Sheet1)'[Customer]),
            "CustomerSales", CALCULATE(SUM('test(Sheet1)'[Sales]))
        ),
        ALLEXCEPT('test(Sheet1)', 'test(Sheet1)'[Store])
    )

VAR RankedTable =
    ADDCOLUMNS(
        CustomerSalesTable,
        "Rank", RANKX(CustomerSalesTable, [CustomerSales], , DESC)
    )

VAR CustomerRank =
    MAXX(
        FILTER(RankedTable, [Customer] = CurrentCustomer),
        [Rank]
    )

RETURN
    IF(
        NOT IsCustomerLevel,
        StoreTotal,
        IF(CustomerRank <= 5, SUM('test(Sheet1)'[Sales]))
    )

The relationship is seen below:

BUT - the problem as said before, is that sometimes thetable does not show all bottom and top 20 stores (40 rows) when i pick a time period. Would love to get some help and appreciate if a draft pbix file could be made, thanks

[Anonymous · Accepted solution]

Hi @derekli1700,

Thank you for reaching out in Microsoft Community Forum.

Please follow below steps for desired output;

1.Rank Stores by sales using RANKX and filter Top 20 and Bottom 20 stores into a combined table.

2.Rank Customers within each store and flag only the Top 5 using a DAX measure.

3.Use a Matrix visual with Store > Customer hierarchy and apply filters for the ranked stores and customers.

4.Create a Sales measure to show total store sales at the store level and top 5 customer sales when drilled down.

Please continue using Microsoft Community Forum.

If this post helps in resolve your issue, kindly consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.

Regards,
Pavan.

[pankajnamekar25]

Hello @derekli1700 

You can explore this blog

DAX (Power BI) – Dynamic TopN made easy with What If Parameter - FourMoo | Microsoft Fabric | Power ...

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.