Forum Discussion
Helper IIrankx 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:
| 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 |
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.
2 Replies
- Mariusz
Community Champion
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.
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
