Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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
Hi, @Anonymous
Is your problem solved?
Best Regards,
Caiyun Zheng
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:
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!
@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)
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
@Anonymous , Day was a weekday, that is what I assumed initially. Sorry for the confusion. Use weekday name in place of day
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...
@Anonymous , if this is for a week(every week), In my current formula, you can filter the rank =1 using the visual level filter
@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)
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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |