Ranking - Finding date for Top sales on a Mon, Tue, ... Sun

[Anonymous]

Hi All,

I've been struggling with the RankX formula all weekend - i've made some progress but still not exactly what I want.

I've got a list of sales, by day. My date table has multiple columns to cut the data one of them being DDD "Mon", "Tue", etc...

I've got to the following ranking formula:

Rank = Rankx(ALLSELECTED(DateTable),[Actual Sales],,DESC,Dense)

Which as Friday is the top day gives these results:

and if i select "mon" or "Tue" it gives me the following correct results...

But really what i want is the week days down the side and date and sales in the next two columns for Rank 1 of each day...

Thanks!

S

[v-cazheng-msft]

Hi, @Anonymous 

Is your problem solved?

Best Regards,

Caiyun Zheng

[Anonymous]

Hi @amitchandak 

thanks for that data really useful...

i think what you have done is rank the days in the week... what i'm after though is the best Monday and the best Tuesday etc..

so in your example i'd have a table like this:

[Anonymous]

Sorry @amitchandak  not sure i follow your last reply... i'm after being able to produce the above table - Ie the best sales for each day across the entire date range...

if i filter by Week Rank for 1 i just get a blank table...

if i filter Rank Weekday = 1 then i just get the top sales day in each week for each week... 

not the top Monday out of all the mondays in the data etc?

thanks again - learning DAX is proving to be a challenge for sure!

[amitchandak]

@Anonymous , for Top Monday/Tuesday/.../.. across all dates in data, Try a measure like this and filter for 1

Rank = Rankx(filter( ALLSELECTED(DateTable[Day],DateTable[date] ), [Day] =max([Day])),[Actual Sales],,DESC,Dense)

[Anonymous]

sorry @amitchandak  in the above formula what is the [Day] column? is it just counting the days from the start of the date table... so if it starts at 01/01/2016 that would be 1 and then 01/01/2017 would be 366 and so on?

Thanks

Scott

[amitchandak]

@Anonymous , Day was a weekday, that is what I assumed initially. Sorry for the confusion. Use weekday name in place of day 

[Anonymous]

Hi @amitchandak 

thanks for the Power BI file really useful set of data!

I dont want to know the rank of the day in the week i want to be able to find best "monday", best "Tuesday" etc...

so in your file i want a table that would do this:

	Date	Sales
Mon	25/06/2018	41,005
Tue	24/07/2018	42,383
Wed	27/06/2018	52,798

and so on...

i can get this from your report by filtering on weekday... and ordering sales... but I'd like a table that changes as i filter or things like store or region or division etc...

[amitchandak]

@Anonymous , if this is for a week(every week), In my current formula, you can filter the rank =1 using the visual level filter 

[amitchandak]

@Anonymous , Have a week number column too in you date table and then try

New column

weekNo = WeekNum([Date],2) //monday week

Assumed day as weekday

new measure

Rank = Rankx(filter( ALLSELECTED(DateTable[Day],DateTable[date],DateTable[weekNo] ), [weekNo] =max([weekNo])),[Actual Sales],,DESC,Dense)

[Anonymous]

Hi @amitchandak ,

thanks for the super quick response...

Not sure if i have the formula exactly as you wrote it... but it doesnt look to be working...

I have these cols in my date table:

many Thanks!

S

[amitchandak]

@Anonymous , as a week no is across the year I end up using Year Week YYYYWW or Rank on this. But all need to be context at the time of disply

Rank Weekday = rankx(filter(ALLSELECTED('Date'[Date],'Date'[Week Rank],'Date'[Weekday Name]), [Week Rank] =MAX('Date'[Week Rank])),[Sales])

refer page Week Rank in the file attached after signature