rankx in DAX

[sentsara]

Hi team,

i need top 5 from each Grp based on val desc

SQL Query

declare @CategoryRanking table
(
Grp varchar(10),
Category varchar(100),
val float
)

insert into @CategoryRanking
select 'HP','HP--0',12
UNION ALL
select 'HP','HP--1',34
UNION ALL
select 'HP','HP--2',65
UNION ALL
select 'HP','HP--3',2
UNION ALL
select 'HP','HP--4',87
UNION ALL
select 'HP','HP--4',98
UNION ALL
select 'HP','HP--5',122
UNION ALL

select 'DELL','DELL-1',455
UNION ALL
select 'DELL','DELL-2',123
UNION ALL
select 'DELL','DELL-3',456
UNION ALL
select 'DELL','DELL-4',100
UNION ALL
select 'DELL','DELL-5',500
UNION ALL
select 'DELL','DELL-6',68
UNION ALL
select 'DELL','DELL-7',300

SELECT * ,ROW_NUMBER() OVER(PARTITION BY grp ORDER BY val desc) AS RowNumb
FROM @CategoryRanking

Expected output:  top 5 value desc from each grp is needed in DAX:

Grp	Category	val	RowNumb
DELL	DELL-5	500	1
DELL	DELL-3	456	2
DELL	DELL-1	455	3
DELL	DELL-7	300	4
DELL	DELL-2	123	5
DELL	DELL-4	100	6
DELL	DELL-6	68	7
HP	HP--5	122	1
HP	HP--4	98	2
HP	HP--4	87	3
HP	HP--2	65	4
HP	HP--1	34	5
HP	HP--0	12	6
HP	HP--3	2	7

[AiolosZhao]

Hi @sentsara ,

You can create a table, make Grp, category and below measure as value, then I think you can got what you want.

measure = IF(RANKX(ALL('Table'[Category]),CALCULATE(SUMX('Table','Table'[val]))) < 6,MAX('Table'[val]))

Please try.

Aiolos Zhao

[Mariusz · Accepted solution]

Hi @sentsara 

For rank column you can use...

Rank x Colunm = 
VAR _rank = CALCULATETABLE( VALUES( YourTable[val] ), ALLEXCEPT( YourTable, YourTable[Grp] ) )
RETURN RANKX( _rank, YourTable[val],, DESC, Dense )

Measure to select top 5

Top 5 Measure = COUNTROWS( FILTER( YourTable, YourTable[Rank x Colunm] <=5 ) )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.