How to make a Top/Bottom 20 Row Slicer for a Matrix Table

[derekli1700]

Hi - currently i have a matrix table that shows all store monthly sales transactions and each store's respective top 5 customer's sales based off a date slicer. The Table's rows are comprised of a DISTINCT table of the stores and customers, Year and Year-Month columns and the Value is a Sales Measure.

 

I'm struggling to make a Tile Slicer that shrinks the table to show Top 20/Bottom 20 stores in the table (depending on the date slicer) which matches up with the results of my data's pivot table on excel (attached). 

My current PBIX file attached as well

To illustrate what i'm doing currently - I made the table's sales value by these measures:

Total Sales = SUM( Overview[Sales])

Then i used this measure as the Matrix Table value:

MEASURE-Sales = 
VAR CurrentRank = [Customer Rank (Fixed)]
VAR IsTop5Customer = IF(NOT ISBLANK(CurrentRank), CurrentRank <= 5, FALSE)

RETURN
IF(
    ISINSCOPE('Unique Customers'[Customer]),
    IF(
        IsTop5Customer,
        [Total Sales],
        BLANK()
    ),
    CALCULATE(
        [Total Sales],
        REMOVEFILTERS('Unique Customers'[Customer])
    )
)

As mentioned before - i also made the table show each store's top 5 customers via this rank:

Customer Rank (Fixed) = 
VAR SelectedDates =
    CALCULATETABLE(
        VALUES('Date'[Date]),
        ALLSELECTED('Date')
    )

VAR CurrentCustomer = SELECTEDVALUE('Unique Customers'[Customer])
VAR CurrentSite = SELECTEDVALUE('Unique Stores'[Store]) 
VAR CustomerSales =
    CALCULATE(
        [Total Sales],
        KEEPFILTERS(SelectedDates),
        ALL('Date'), 
        'Unique Stores'[Store] = CurrentSite
    )

RETURN
IF(
    ISINSCOPE('Unique Customers'[Customer]),
    IF(NOT ISBLANK(CustomerSales) && CustomerSales > 0,
        RANKX(
            FILTER(
                ALL('Unique Customers'[Customer]),
                CALCULATE(
                    [Total Sales],
                    KEEPFILTERS(SelectedDates),
                    ALL('Date'),
                    'Unique Stores'[Store] = CurrentSite
                ) > 0
            ),
            CALCULATE(
                [Total Sales],
                KEEPFILTERS(SelectedDates),
                ALL('Date'),
                'Unique Stores'[Store] = CurrentSite
            ),
            ,
            DESC,
            DENSE
        )
    )
)

My date table is below for reference (capped at current month for YOY measure purposes not mentioned above):

Date = 
ADDCOLUMNS (
    CALENDAR (
        DATE (2023, 1, 1),
        DATE (2025, 4, 30)
    ),
    "Year", YEAR([Date]),
    "Year-Month", FORMAT([Date], "yyyy-MM"),
    "Year-Month sort", EOMONTH([Date], 0)
)

Would like some guidance as the numbers dont match with my excel pivot table numbers when i select a specific date range/overall date range or skew/exceeds query resources if Year-Month is added to the rows. 

[Ashish_Excel · Accepted solution]

Hi,

Try this measure

Measure = if(ISINSCOPE('Unique Customers'[Customer]),CALCULATE([Total Sales],TOPN(5,ALL('Unique Customers'[Customer]),[Total Sales]),VALUES('Unique Customers'[Customer])),CALCULATE([Total Sales],TOPN(5,ALL('Unique Stores'[Store]),[Total Sales]),VALUES('Unique Stores'[Store])))

[v-echaithra]

Hi @derekli1700 ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

Hi @derekli1700 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra · Accepted solution]

Hi @derekli1700 ,

Thanks for reaching out to Microsoft Community.

Please refer to this document which helps you to implement a toggleable Top 20/Bottom 20 Store Slicer.
Dynamically Display Top N and Bottom N Records in One Power BI Chart
How to configure a Top X Slicer in Power BI | Magnetism Solutions | NZ (Auckland, Wellington, Christ...

If this post helps, please give us Kudos and consider marking it Accept as solution to assist other members in finding it more easily.

Regards,
Chaithra.

[Ashish_Excel · Accepted solution]

Hi,

Try this measure

Measure = if(ISINSCOPE('Unique Customers'[Customer]),CALCULATE([Total Sales],TOPN(5,ALL('Unique Customers'[Customer]),[Total Sales]),VALUES('Unique Customers'[Customer])),CALCULATE([Total Sales],TOPN(5,ALL('Unique Stores'[Store]),[Total Sales]),VALUES('Unique Stores'[Store])))

[derekli1700]

is there a way to make it into a tile slicer so that it can shrink once they select "top 20" or "bottom 20" instead of it always being shrunk?

[Ashish_Excel]

Yes but that requires some work.  Will take my time which i do not want to onvest right now.