Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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 ) )
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
Proud to be a Super User!
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 ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |