DAX Ranking by group

[FrancescaLu]

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

[Jos_Woolley · Accepted solution]

Hi,

Calculated Column:

MyRank =
VAR ThisGroup = 'Table'[Groups]
RETURN
    RANKX(
        FILTER(
            'Table',
            'Table'[Groups] = ThisGroup
        ),
        'Table'[Amount],
        ,
        ,
        DENSE
    )

Regards

[KartikWatt]

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 below

Store 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

[FrancescaLu]

It works! 

Thank you very much