Matrix how to Sort within Nested Rows?

[AlexS2023]

Hi all,

I have a matrix showing how many employees we've assigned to a given "cost center" by date, with additional rows to show the employee names. This worked great, but now I've been asked to include the Employee's Job-Title too, which is proving trickier than it sounds...

If I just add Job-Title to the rows, I get a third level of nested data, which is not helpful at all, but I can't add it to the columns because that then repeats the timeline for every possible job-title!

So, I created a custom column to show the Job-Title next to the Employee name (as if it was a second column in the Y axis - see screenshot). This moved me in the right direction, but leaves two Questions:

1. The records are sorted alphabetical by Employee Name, it would be much more useful to sort by Job-Title. Is there a way I can do this? I *could* just swap the name for the job-titles in the custom column (which would be an acceptable compromise) except...

2. I need to add a slicer, so managers can filter the employees by job-title. I figure this means I *have* to get the actual job-title into the matrix somewhere, but I don't want it to be visible. is there anyway to add the data without screwing with anything else?

Open to all ideas and workarounds 🙂

TIA

AS

[audreygerred · Accepted solution]

For point 1 - I agree, just swap it.

For point 2 - you can have a slicer on the canvas or put the field in teh filter pane that only has the job title and the user can use that to filter to what they want - it doesn't need to be part of the matrix visual. Another option if you would prefer to keep name and title separate it to simply turn off the stepped layout under the Rows settings.

[audreygerred · Accepted solution]

For point 1 - I agree, just swap it.

For point 2 - you can have a slicer on the canvas or put the field in teh filter pane that only has the job title and the user can use that to filter to what they want - it doesn't need to be part of the matrix visual. Another option if you would prefer to keep name and title separate it to simply turn off the stepped layout under the Rows settings.

[AlexS2023]

Thanks AudreyGerred. I was clearly overthinking this one.

Just on point 1, do you think there would be a method (to sort by a hidden field)? I know I can live without it - but I can already here my manager asking anyway 😂

Thanks,

[audreygerred · Accepted solution]

Hi! You absolutely can, depending on what it is you want to sort. For example, if you want to sort title in a non-alphabetical way, you can assign each item a numerical value in the order you want, then sort the title by the numeric value - this way when you use Title, it is sorted in the order you assigned without having to have the numeric value actually in the visual (this is also the same way you would have month name sorted correctly by chronology, not alphabetically). Sort one column by another column in Power BI - Power BI | Microsoft Learn