Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Bonkers
Frequent Visitor

Unusual sort behaivor in Matrix/Table-Visual

Hey,

 

I am currently struggling with the way my table and matrix visuals are displayed in powerbi.

 

My idea is a hierarchical representation of a ProfitAndLoss or Costform according to a fixed order of hierarchy.

The Hierachy is set up in several columns [Level 1] - [Level 11] for each Row. Furthermore there is one more column which shows me the correct order on Level 1.

 

When I insert my hierarchy into a table-visual, it is not sorted correctly if I use the table tools to sort level 1 with the sort order column, which is very inconvenient because I actually need to use the matrix-visual for the visualisation of this report. [Level 1] is unique. The sort order column is correct, because if I integrate the sort order column into the table-visual and sort it in ascending order in the visual, it shows me the order I expect.  

 

Does anyone have any experience in this case ?

 

Many Thanks in Advance

 

Best Regards

Bonkers

1 REPLY 1
johnbasha33
Super User
Super User

@Bonkers 

It sounds like you're encountering some challenges with sorting your hierarchical data in Power BI, especially when using a matrix visual. Here are some suggestions to address your issue:

1. **Sort by Sort Order Column**: Since you've mentioned that the sort order column works correctly in the table visual, you can try applying sorting directly to the matrix visual using the same sort order column. In the matrix visual, select the column header for [Level 1], go to the modeling tab, and then choose "Sort by Column". Select your sort order column to ensure that the matrix visual is sorted according to your desired hierarchy.

2. **Use a Custom Sorting Column**: If the above method doesn't work as expected, you can create a custom column in your dataset that concatenates the values of [Level 1] and the sort order column. For example, you can create a new column named "Custom Sort" with a formula like [Level 1] & "-" & [Sort Order]. Then, sort the matrix visual by this custom sorting column.

3. **Sort Using Power Query**: Another approach is to apply sorting within Power Query before loading the data into Power BI. You can sort your data based on the [Level 1] column and the sort order column using Power Query's sorting capabilities. This ensures that your data is sorted correctly before being visualized in Power BI.

4. **Check Data Type**: Ensure that the data type of your sort order column is set correctly in both Power Query and Power BI. If the sort order column is treated as text instead of a numeric or integer data type, sorting may not work as expected.

5. **Review Matrix Visual Settings**: Double-check the matrix visual settings to ensure that there are no additional sorting or filtering applied that may interfere with the desired hierarchy. Also, make sure that the matrix visual is configured to display the [Level 1] column as the row headers.

By applying these strategies, you should be able to sort your hierarchical data correctly in both table and matrix visuals in Power BI, providing you with the desired visualization of your Profit and Loss or Costform data.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.