Power BI Pro Tip: Sort By Column
Here is an easy solution to a very common problem when making reports: how do I change the sort of some items from alphabetically to something else?
The most apparent sample of this is Months. Let’s say you have to following (very simple) data model in Power Pivot:
That is, you have a Sales table that reports SalesAmount on a Date and related to that Sales table is a Date table (dimension) which stores dates and month name.
Now you create a PivotTable and provide a slicer to filter:
Or you create a Power View report:
What’s wrong here? Your users probably want the months ordered correctly not alphabetically by month name as they are now in both the slicer and the Power View graph.
Of course you can give the months a numeric prefix like ’01 – January’, ’02 – February’, etc. This may work perfectly fine for you but I think this approach is impacting the user interface to much.
There is, naturally, a better way. And it is very easy to implement. All you need is two modifications to the date table.
- In Power Pivot go to the date table and add a calculated column with the following formula: =Month([Date]) :
- Then click on the column that contains the Monthname (my second column) and choose Sort By Column:
- In the dialog choose MonthNumber as your column to sort by and click OK:
- Done.
Now go back to Excel and look at the slicer and your Power View report. Both of them now sort correctly thanks to the power of Sort by Column. This of course is applicable to anything, not just months or dates.
That’s it for this Power BI Pro Tip. Until next time!
Share this:
- Click to share on LinkedIn (Opens in new window)
- Click to share on Facebook (Opens in new window)
- Click to share on Twitter (Opens in new window)
- Click to share on Skype (Opens in new window)
- Click to share on WhatsApp (Opens in new window)
- Click to share on Pocket (Opens in new window)
- Click to share on Tumblr (Opens in new window)
- Click to share on Pinterest (Opens in new window)
- Click to share on Telegram (Opens in new window)
- Click to share on Reddit (Opens in new window)
- Click to print (Opens in new window)
- Click to email this to a friend (Opens in new window)
Related
You May Also Like

Updated: Annual radio countdown Top 2000 in Power BI
January 3, 2019
Updated: Annual Radio Countdown Top 2000 in Power BI
December 23, 2020