Sorting formatted day of the week as column header in a matrix?

[Anonymous]

Hi,

Hi, I'm having trouble with this matrix visualisation. I have a calculated column in my transaction table which formats the session date as DDDD to give me the day of the week. But when I import this into my visual it sorts the days alphabetically which is not useful. Any advice on how to sort this? I'm due to give a presentation on it very soon and I can't fix it. I tried adding the day of the week from the date table which fixes the issue but then for some reason it sorts it by transaction date rather than session date (which is fine for income summaries but not useful in this visual which is to do with average occupancy).

Thanks!

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reprouce your scenario. The pbix file is attached in the end.

Table:

Weekday(a calculated column):

Weekday = FORMAT('Table'[Date],"dddd")

You may create a sort column like below.

Sorted Weekday = WEEKDAY('Table'[Date])

Then you need to make the column 'Weekday' selected, click 'Sort by column' under 'Column tools' tab,  sort by 'Sorted Weekday'.

Result:

Best Regards

Allan

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

[amitchandak]

@Anonymous , Create a sort number based on day

Week day 1 = [Week day]

Week Day Sort = switch([Week day], "Sumday",1,"Monday",2,"Tuesday",3) //Add all

Then sort week day on Week Day Sort , if that gives error then sort week Day 1 on Week Day Sort  and use Week Day 1

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column