cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sentsara Regular Visitor
Regular Visitor

rankx in DAX

 

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:

 

 

GrpCategoryvalRowNumb
DELLDELL-55001
DELLDELL-34562
DELLDELL-14553
DELLDELL-73004
DELLDELL-21235
DELLDELL-41006
DELLDELL-6687
HPHP--51221
HPHP--4982
HPHP--4873
HPHP--2654
HPHP--1345
HPHP--0126
HPHP--327
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: rankx in DAX

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.
Mariusz Repczynski


 

View solution in original post

2 REPLIES 2
Super User
Super User

Re: rankx in DAX

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.
Mariusz Repczynski


 

View solution in original post

AiolosZhao Established Member
Established Member

Re: rankx in DAX

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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)