DAX Ranking within Group

[KartikWatt]

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 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

[Anonymous · Accepted solution]

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.

[DallasBaba]

@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