How to show bottom/top N in a Matrix table DYNAMICALLY

[derekli1700]

Here is my sample sheet: TEST SHEET1.xlsx
I would like to see how to make my table display the Top and Bottom 20 sites and their respective top 5 customers based off a time range visual. E.g) If i pick Jan to March 2025 - the table will automatically include only the top and bottom 20 sites (40 rows) and their top 5 customers.

Basically like this:

The problems i keep running into are (which i verify by using a pivot table in excel):
1. The store total sales value being the sum of the top 5 customers rather than the overall store sum in that time range

2. The top/bottom 20 + top 5 rows being undone once a time range is selected

3. Sometimes in a time range, when you pick a site and see their top 5 customers, there are not 5 rows which shouldnt be the case.

Would like for help or someone to take a crack at this -

[ajaybabuinturi]

Hi @derekli1700,

I would recomend to follow the below steps to achive requirement.

1. Total Sales = SUM(Overview[Vol (L)])
2. Site_Rank = RANKX(ALLSELECTED(Overview[Site - 2]), [Site_Sales], ,DESC)
3. Site_Sales = CALCULATE(SUM('Overview'[Vol (L)]), REMOVEFILTERS('Overview'[Customer - 2]))
4. //Add to visual level filter
   Top_Bottom_# =
   Var TotalSites = CALCULATE(DISTINCTCOUNT(Overview[Site - 2]), ALLSELECTED(Overview))
   Var S_Rank = [Site_Rank]
   RETURN
   IF(S_Rank <= 'Site TOP-Bottom #'[Site TOP-Bottom # Value] || S_Rank > TotalSites - 'Site TOP-Bottom #'[Site TOP-Bottom # Value], 1, 0)
5. //Add to visual level filter
   IsTopNCust =
   var TopCust = CALCULATETABLE(TOPN('CustomerTOP#'[CustomerTOP# Value],ALL(Overview[Customer - 2]),[Total Sales]),ALLSELECTED())
   return
   IF(SELECTEDVALUE(Overview[Customer - 2]) IN TopCust,1,0)
6. To achive dynamic TOP Bottom and TOP customer create parameters
7. CustomerTOP# = GENERATESERIES(1, 1000, 1)
8. Site TOP-Bottom # = GENERATESERIES(1, 1000, 1)

I am sharing .pbix file for your reference.
Click here to Download .pbix file 

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[v-dineshya]

Hi @derekli1700 ,

Thank you for reaching out to the Microsoft Community Forum.

Step 1: Create Key Measures

Total Sales = SUM('Table'[Sales])

Step 2: Create a Site Ranking Measure

Site Rank =
RANKX(
ALLSELECTED('Table'[Site]),
CALCULATE([Total Sales]),
,
DESC,
DENSE
)

Note: Use this to get Top 20, and a similar one for Bottom 20:

Bottom Site Rank =
RANKX(
ALLSELECTED('Table'[Site]),
CALCULATE([Total Sales]),
,
ASC,
DENSE
)

Step 3: Create a Filter Measure to Include Only Top/Bottom 20 Sites

Site Filter =
IF(
[Site Rank] <= 20 || [Bottom Site Rank] <= 20,
1,
0
)

Step 4: Create Customer Ranking within Each Site

Customer Rank Per Site =
RANKX(
FILTER(
ALLSELECTED('Table'),
'Table'[Site] = MAX('Table'[Site])
),
[Total Sales],
,
DESC,
DENSE
)

Step 5: Create a Final Filter Measure to Control Visual

Visual Filter =
IF(
[Site Filter] = 1 && [Customer Rank Per Site] <= 5,
1,
0
)

Step 6: Apply the Filter in the Matrix Visual

Place your Matrix like: Rows: Site, then Customer Values: [Total Sales]

Add a Visual-level filter using the [Visual Filter] measure with filter set to is 1.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[derekli1700]

Hi, it appears the query seems to exceed the resources given the large rows of data, is there a way to bypass this

[v-dineshya]

Hi @derekli1700 ,

Thank you for reaching out to the Microsoft Community Forum.

Please follow below steps for optimization.

1. Pre-Aggregate Data in Power Query:
Instead of ranking and filtering dynamically in DAX, you can pre-aggregate in Power Query or the data source:
Group data by Site and Customer. Compute Total Sales there.Keep only the top/bottom 20 sites and top 5 customers per site.
Load this trimmed dataset into Power BI for visual use

2. Use Variables Inside Measures:

Site Rank =
VAR CurrentSite = MAX('Table'[Site])
RETURN
RANKX(
ALLSELECTED('Table'[Site]),
CALCULATE([Total Sales]),
,
DESC,
DENSE
)
Note: Do this for every ranking measure to reduce recalculations.

3. Avoid ALLSELECTED if Unnecessary:

Site Rank =
RANKX(
ALL('Table'[Site]),
[Total Sales],
,
DESC,
DENSE
)

4. Limit Visible Records in Visual (Top N Visual Filter):
You can filter visuals directly using a Top N filter in the visual pane:
In the matrix visual: Select the Site field. Go to Filters pane. Choose “Top N” and enter 20 using Total Sales. This avoids doing ranking manually in DAX for that part.

5. Use Calculation Groups for Cleaner Logic:
Use calculation groups in Tabular Editor to encapsulate ranking logic and reduce duplicated DAX measures.

6. Move to Paginated Report or DirectQuery for Large Data:
If the dataset is huge and interactivity is required at scale: Consider Paginated Reports (for static exports). Use DirectQuery mode with indexing on Site and Customer

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[johnt75 · Accepted solution]

You can define some measures as below

DEFINE
	MEASURE 'Overview'[Total Vol] = SUM(Overview[Vol (L)])
	MEASURE 'Overview'[Site Rank] = RANK(
			ALL(Sites),
			ORDERBY(
				CALCULATE(
					[Total Vol],
					REMOVEFILTERS(Customers)
				),
				DESC
			)
		)
	MEASURE 'Overview'[Site is visible] = VAR SiteRank = [Site Rank]
		VAR TotalSites = COUNTROWS(ALL(Sites))
		VAR Result = IF(
			SiteRank <= 20 || SiteRank >= (TotalSites - 20),
			1
		)
		RETURN
			Result

	MEASURE 'Overview'[Customer is visible] = VAR CustomerRank = [Customer Rank]
		VAR Result = IF(
			(CustomerRank <= 5 && NOT ISBLANK(CustomerRank))
			|| NOT ISINSCOPE(Customers[Customer]),
			1
		)
		RETURN
			Result
	MEASURE 'Overview'[Customer Rank] = VAR BaseTable = ADDCOLUMNS(
			CALCULATETABLE(
				SUMMARIZE(
					Overview,
					Customers[Customer],
					Sites[Site]
				),
				REMOVEFILTERS(Customers)
			),
			"@val", [Total Vol]
		)
		VAR Result = RANK(
			SKIP,
			BaseTable,
			ORDERBY(
				[@val],
				DESC
			),
			PARTITIONBY(Sites[Site])
		)
		RETURN
			Result
	MEASURE 'Overview'[Total Vol for matrix] = IF(
			ISINSCOPE(Customers[Customer]),
			[Total Vol],
			CALCULATE(
				[Total Vol],
				REMOVEFILTERS(Customers)
			)
		)

Add the site, customer and [total for matrix] into a matrix visual and add filters so that [Site is visible] is 1 and [Customer is visible] is 1. See attached PBIX for reference.