Forum Discussion
DAX Ranking by group
Hi,
I'm getting some problems finding the right syntax to create a new column which has to contain ranking values based on another column.
I have a table made like this:
| Groups | Amount |
| A | 10 |
| A | 20 |
| A | 5 |
| A | 40 |
| B | 30 |
| B | 20 |
| C | 10 |
| C | 5 |
| C | 10 |
And i'm expecting this result:
| Groups | Amount | Rank |
| A | 10 | 3 |
| A | 20 | 2 |
| A | 5 | 4 |
| A | 40 | 1 |
| B | 30 | 1 |
| B | 20 | 2 |
| C | 10 | 1 |
| C | 5 | 2 |
| C | 10 | 1 |
I tried using this DAX formula: Rank = RANKX(ALL('Table'[Groups]), 'Table'[Amount]), but i'm getting "1" for all rows.
I need to have the correct result in my table, is that possible in some way?
Thank you very much,
Francesca
Hi,
Calculated Column:
MyRank = VAR ThisGroup = 'Table'[Groups] RETURN RANKX( FILTER( 'Table', 'Table'[Groups] = ThisGroup ), 'Table'[Amount], , , DENSE )Regards
3 Replies
Hi,
Calculated Column:
MyRank = VAR ThisGroup = 'Table'[Groups] RETURN RANKX( FILTER( 'Table', 'Table'[Groups] = ThisGroup ), 'Table'[Amount], , , DENSE )Regards
It works!
Thank you very much
Hello PBI Experts/ Jos Jos_Woolley
Expected Output :
* A table with 7 distinct rows with each row representing a day of a week and 3 columns : Shop Name, Day Name, Net Sales
I tried various methods, however not getting the desired results.
Any help would be great saviour !
Cheers,K
Attaching sample dummy data belowStore Name Revenue FY Week WeekDayName Aldgate High Street 77 5389 40/2023 Friday Aldgate High Street 77 4326 40/2023 Monday Aldgate High Street 77 3296 40/2023 Saturday Aldgate High Street 77 5402 40/2023 Sunday Aldgate High Street 77 4697 40/2023 Thursday Aldgate High Street 77 4844 40/2023 Tuesday Aldgate High Street 77 5758 40/2023 Wednesday Baker Street 132 2856 40/2023 Friday Baker Street 132 4934 40/2023 Monday Baker Street 132 4240 40/2023 Saturday Baker Street 132 2933 40/2023 Sunday Baker Street 132 1445 40/2023 Thursday Baker Street 132 5577 40/2023 Tuesday Baker Street 132 3651 40/2023 Wednesday Baker Street 15 5083 40/2023 Friday Baker Street 15 3208 40/2023 Monday Baker Street 15 1318 40/2023 Saturday Baker Street 15 4414 40/2023 Sunday Baker Street 15 2226 40/2023 Thursday Baker Street 15 2537 40/2023 Tuesday Baker Street 15 4917 40/2023 Wednesday Bath Stall Street 5902 40/2023 Friday Bath Stall Street 3043 40/2023 Monday Bath Stall Street 5970 40/2023 Saturday Bath Stall Street 5131 40/2023 Sunday Bath Stall Street 1968 40/2023 Thursday Bath Stall Street 3227 40/2023 Tuesday Bath Stall Street 5922 40/2023 Wednesday Battersea Power Station 5703 40/2023 Friday Battersea Power Station 3288 40/2023 Monday Battersea Power Station 3855 40/2023 Saturday Battersea Power Station 5844 40/2023 Sunday Battersea Power Station 2697 40/2023 Thursday Battersea Power Station 4645 40/2023 Tuesday Battersea Power Station 4489 40/2023 Wednesday Bicester Village 3441 40/2023 Friday Bicester Village 4699 40/2023 Monday Bicester Village 4613 40/2023 Saturday Bicester Village 1962 40/2023 Sunday Bicester Village 2158 40/2023 Thursday Bicester Village 3916 40/2023 Tuesday Bicester Village 4077 40/2023 Wednesday
