Keep sort order when going down hierarchies

Hi Everyone - hoping some can help with this.

I have a chart with the X-axis being dates. There are three hierarchies on the axis being FY -> Quarter -> Month. Because the fiscal year starts in October, I have a “month sort” column in my date tables so that October is first, November, second, etc. This works fine.

The issue is that the sort in the chart changes when you move down a hierarchy versus expanding it. Here are two examples. The chart on the left is sorted correctly after one expands the hierarchy. But if you go down the hierarchy (as I’m not showing the fiscal year and quarter to save space) - it puts October first.

I see what is happening in that if you use the “sort by” feature - the first [left[ chart gives the option to sort by “FY Quarter Month” as those three levels are visible. But the second chart just gives the “Month” as the sort option and thus moves October first.

I can just concatenate the axis one the left to make it work, but prefer the cleaner version on the right.

Chart Pic 1 Chart Pic 2

Any thoughts? Thanks,


The quick and easy fix is to set bookmarks for all the hierarchy levels without having to worry about the sort order.
Another way is to Assign Alphabetical Ranks to Months and then sort it by Alphabets. For e.g.
If Month = October , “A”,
If Month = November , “B” etc.
Or you can share the file so we can look at it in detail.


@MudassirAli - thanks for the reply. I do currently use a ranking on the months per your example but just use numbers instead of letters. I like the bookmark idea (especially if one wants to toggle many reports) but the problem I’m running into is that the bookmark just captures the same visual.

So - after spending more time on this then needed, I opted to just create an additional column in my dates table for the abbreviated month name, concatenated the x-axis, and then expanded the hierarchy.

So it now looks a little cleaner.

Pic 3

But a take away here is the idea on using the bookmarks to toggle between views.


1 Like