Forum Discussion

KartikWatt's avatar
KartikWatt
Regular Visitor
2 years ago
Solved

DAX Ranking within Group

Hello PBI Experts

I am struggling with a DAX calculation. 

 

I have attached a sample data below for which I expecting following 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
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi KartikWatt ,

    Please refer to my pbix file to see if it helps you.

    Create 2 measures.

    Measure =
SUMX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Store Name] = SELECTEDVALUE ( 'Table'[Store Name] )
    ),
    'Table'[Revenue]
)

    Measure2 =
VAR _1 =
    RANKX ( ALL ( 'Table' ), CALCULATE ( [Measure] ),, desc, DENSE )
RETURN
    IF ( MAX ( 'Table'[weekday] ) = _1, _1, BLANK () )

     

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

2 Replies

  • DallasBaba's avatar
    DallasBaba
    Skilled Sharer
    2 years ago

    KartikWatt You can try the SUMMARIZECOLUMNS function to group the data based on two columns: 'Store Name' and 'WeekDayName'. This creates a table with distinct combinations of these two columns.

    SummaryTable =
    SUMMARIZECOLUMNS (
    'SampleData'[Store Name],
    'SampleData'[WeekDayName],
    "Net Sales", SUM('SampleData'[Revenue])
    )

    =============OR

    DistinctTable =
    ADDCOLUMNS(
    SUMMARIZE(
    'SampleData',
    'SampleData'[WeekDayName],
    'SampleData'[Store Name]
    ),
    "Net Sales", SUM('Table'[Revenue])
    )


    Please let me know if that helps.

    Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi KartikWatt ,

    Please refer to my pbix file to see if it helps you.

    Create 2 measures.

    Measure =
SUMX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Store Name] = SELECTEDVALUE ( 'Table'[Store Name] )
    ),
    'Table'[Revenue]
)

    Measure2 =
VAR _1 =
    RANKX ( ALL ( 'Table' ), CALCULATE ( [Measure] ),, desc, DENSE )
RETURN
    IF ( MAX ( 'Table'[weekday] ) = _1, _1, BLANK () )

     

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.