Forum Discussion

FrancescaLu's avatar
FrancescaLu
Frequent Visitor
4 years ago
Solved

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 ...
  • Jos_Woolley's avatar
    Jos_Woolley
    4 years ago

    Hi,

    Calculated Column:

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

    Regards

Jos_Woolley's avatar
Jos_Woolley
Solution Sage
4 years ago

Hi,

Calculated Column:

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

Regards

KartikWatt's avatar
KartikWatt
Regular Visitor
2 years ago

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 NameRevenueFY WeekWeekDayName
Aldgate High Street 77538940/2023Friday
Aldgate High Street 77432640/2023Monday
Aldgate High Street 77329640/2023Saturday
Aldgate High Street 77540240/2023Sunday
Aldgate High Street 77469740/2023Thursday
Aldgate High Street 77484440/2023Tuesday
Aldgate High Street 77575840/2023Wednesday
Baker Street 132285640/2023Friday
Baker Street 132493440/2023Monday
Baker Street 132424040/2023Saturday
Baker Street 132293340/2023Sunday
Baker Street 132144540/2023Thursday
Baker Street 132557740/2023Tuesday
Baker Street 132365140/2023Wednesday
Baker Street 15508340/2023Friday
Baker Street 15320840/2023Monday
Baker Street 15131840/2023Saturday
Baker Street 15441440/2023Sunday
Baker Street 15222640/2023Thursday
Baker Street 15253740/2023Tuesday
Baker Street 15491740/2023Wednesday
Bath Stall Street590240/2023Friday
Bath Stall Street304340/2023Monday
Bath Stall Street597040/2023Saturday
Bath Stall Street513140/2023Sunday
Bath Stall Street196840/2023Thursday
Bath Stall Street322740/2023Tuesday
Bath Stall Street592240/2023Wednesday
Battersea Power Station570340/2023Friday
Battersea Power Station328840/2023Monday
Battersea Power Station385540/2023Saturday
Battersea Power Station584440/2023Sunday
Battersea Power Station269740/2023Thursday
Battersea Power Station464540/2023Tuesday
Battersea Power Station448940/2023Wednesday
Bicester Village344140/2023Friday
Bicester Village469940/2023Monday
Bicester Village461340/2023Saturday
Bicester Village196240/2023Sunday
Bicester Village215840/2023Thursday
Bicester Village391640/2023Tuesday
Bicester Village407740/2023Wednesday