topic Re: Dynamic Rank Calculation in DAX Commands and Tips

Dynamic Rank Calculation

Amit92 — Tue, 07 Apr 2026 12:28:12 GMT

Hi Team

I have a model where i have Dim Market, Dim Brand and Fact Sales.
Market ID of Dim Market is connected to Market ID of Fact Sales
Brand ID of Dim Brand is connected to Brand ID of Fact Sales
We have a brand named "Kingfisher" in the Dim Brand

I need to create a measure with below logic:

Based on the slicer selection, I want the count of markets where the brand "Kingfisher" is coming on top 5 for a measure named Sales

In Short:

Count the number of markets where “Kingfisher” appears in the Top 5 brands by Sales, respecting the current filter context.

Thanks
Amit

Re: Dynamic Rank Calculation

johnt75 — Tue, 07 Apr 2026 12:40:24 GMT

I think you can use

Num markets Kingfisher Top 5 = VAR MarketsAndBrands = ADDCOLUMNS ( CALCULATETABLE ( SUMMARIZE ( Sales, Market[Market], Brand[Name] ), ALLSELECTED () ), "@sales", [Sales] ) VAR Top5 = WINDOW ( 1, ABS, 5, ABS, MarketsAndBrands, ORDERBY ( [@sales], DESC ), PARTITIONBY ( Market[Market] ) ) VAR Result = COUNTROWS ( FILTER ( Top5, Brand[Name] = "Kingfisher" ) ) RETURN Result

Re: Dynamic Rank Calculation

Amit92 — Tue, 07 Apr 2026 12:56:10 GMT

Hi John,

Thanks for the quick help,
I am getting error in the above measure that the column Market[Market] specified in the 'SUMMARIZE' function was not found in the input table

Re: Dynamic Rank Calculation

cengizhanarslan — Tue, 07 Apr 2026 13:09:25 GMT

Please try the measure below:

Kingfisher Top 5 Markets = COUNTROWS ( FILTER ( VALUES ( 'Dim Market'[MarketID] ), VAR _KingfisherSales = CALCULATE ( [Sales], 'Dim Brand'[Brand] = "Kingfisher" ) VAR _Rank = RANKX ( ALLSELECTED ( 'Dim Brand'[Brand] ), CALCULATE ( [Sales] ), _KingfisherSales, DESC, Dense ) RETURN _Rank <= 5 ) )

Re: Dynamic Rank Calculation

johnt75 — Tue, 07 Apr 2026 13:13:35 GMT

Change both references to Market[Market] to whichever column from your market dimension uniquely identifies a row. e.g. you could use Market[Market ID].