Forum Discussion
Hierarchy sorting
Hi tearless
To sort a hierarchy properly in Power BI, you need a separate sort column for each level of the hierarchy. In your case, since the hierarchy goes down to 9 levels, you should create individual sort columns for each level. These sort columns will define the specific order for each hierarchy level.
Example:
Let's assume your hierarchy has the following levels:
- Level 1: ReportingLevel01
- Level 2: ReportingLevel02
- ...
- Level 9: ReportingLevel09
You need to create sort columns like:
- SortLevel01 for ReportingLevel01
- SortLevel02 for ReportingLevel02
- ...
- SortLevel09 for ReportingLevel09
Each sort column should have a unique value for each row to define the sorting order for that level.
Once you have these sort columns, you can set the sorting for each hierarchy level in Power BI by going to the column tools and selecting "Sort by Column" and choosing the appropriate sort column for each hierarchy level.
By doing this, you ensure that each level of the hierarchy is sorted in the desired order.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
This is what I did.
I created hierarchy tables Level1... to Level 9 with ID that shows the order of each hierarchy member.
Then I joined these 9 tables with the data table which has those hierarchy member.
Then, I selected each hierarchy table and sorted by those IDs..
But the report still does not get sorted as needed.
Hi tearless
If you’ve correctly defined a sort order for each level in the hierarchy (numeric, which is required), sorted all the tables, and placed the dimensions from the relevant tables into the visualization, with the visualization itself sorted by what’s in the 'Rows', it should work. If it’s not working, something must have gone wrong along the way. To figure it out, I’d need to see the file. Please share a link to it (without sensitive data), and I’ll be happy to check it. Alternatively, I’m attaching a file I created for you with 4 hierarchies—this is enough to understand the logic, as it’s not dependent on the number of levels in the hierarchy.Results :
+ you can download the pbix and follow my steps
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
