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 wellTo 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.

Hi,Try this measureMeasure = 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])))

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

Forum Discussion

Related content

show grand totals top/bottom of matrix

(Top/Bottom) 5 Slicer

Subtotals in Matrix top and bottom

Move Total from Matrix Table to the top instead of the bottom

Slicer + Matrix