Matrix Sorting

[Sana-Shaikh]

Hello,

I have a table that has columns Dept Names, Employees and hours worked. I created a matrix that shows Dept Names and Employees as rows and hours worked as a column. I want to sort the total hours worked for each dept in DESC order, simutaneously, I want to sort the Employee IDs in ASC order regardless of how many hours they worked. I am trying to create a calculated column to concatenate but am having trouble figuring out what I am doing wrong. 

Is there anyone who can help me with this?

[Ritaf1983]

Hi @Sana-Shaikh 

please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

[Sana-Shaikh]

@Ritaf1983 

Sorry about that. So this would eb an example of the visual I currently have. I have it sorted DESC by Hours worked. For the matrix I want, teh hours to be the same as they are now, in descending order, but the Employee IDs need to be rearraged to be in ascending order such as:

So the Total/Sum of the Hours worked is Desc order, while the Employee ID's under the department umbrella is in Asc order. Is there a DAX column that can achieve these results?

[Ritaf1983]

Hi @Sana-Shaikh 
If I understood you correctly you trying to achieve a different sort of order by hierarchies.
Take a look at the workaround in the linked blog post + linked discussion :
https://stuffbyyuki.com/hierarchical-sorting-in-power-bi/

https://community.fabric.microsoft.com/t5/Desktop/PowerBi-hierarchy-sort-multiple-levels/td-p/276394...

If my answer was helpful please give me a Kudos and accept as a Solution.

[Sana-Shaikh]

@Ritaf1983 ,
I don't want to create a new column for sort. 

The values don't match what I would want. The Total values that are bolded would be in descending order: 5500, 2800, 1500, 1500 for this 
The subgroups would be ordered Small, Medium, Big for each Product Category 

[Ritaf1983]

Hi, @Sana-Shaikh  to sort different levels by different "order types" you still need to add something that will manage the wanted order because we don't have this functionality as an option for the visuals.
If you need it dynamic it must be a column of your matrix. 

After sorting you can minimize the sort manager column width to "hide" it and leave just a sort rectangle

To create this logic you need :
1. To have a sort column for the IDs with the numeric value like :

2. Sort sub-categories by this column from the table view

3. Create a sort manager measure:

sort rank =

var rank_value= RANKX(ALLSELECTED('Table'[Category]),[Sum_],,DESC)

Return

If(ISINSCOPE('Table'[Sub category]),max('Table'[Sort manager]),rank_value)

4. Sort by this column :

5. To Hide the column but leave the rectangle visible for users :
Rename it on visual :

format column values and totals as a white color :

Result :

PBIX is Attached

If my answer was helpful please give me a Kudos and accept as a Solution.

[Sana-Shaikh]

@Ritaf1983 
Let's try this from a different approach. When I create the matrix, the system already puts the sub group (Employee ID's) in ASC order. Now what I would like to do is instead of doing Sort by Column for hours(which is a column value), I want to create a calculated column or measure that will sort the Total Hours by department in DESC order

Let's just use the image you gave by example

The Sum per group is in desc order. the subgroups are in asc order. Without using Sort by Column, an this be achieved?

[Ritaf1983]

Hi @Sana-Shaikh 
No.

If my answer was helpful please give me a Kudos and accept as a Solution.

[Sana-Shaikh]

@Ritaf1983 ,

I understand how to do a sort column, I want to know if there is a way to accomplish this without a sort column, maybe with a concatenation? In case Employee Id's change.

[Ritaf1983]

Hi @Sana-Shaikh 
I don't think that there is another dynamic alternative to this issue.

If my answer was helpful please give me a Kudos and accept as a Solution.