Sort by Column with different values for same item or add hidden characters

[ivan_larson_cki]

I have a matrix displaying various amounts broken down by three Row values, let's say Type.A, Type.B, and Item.C. Each value of Item.C has a ranking assigned it for its priority against other items in the same Type.A value. Within each Type.A, I'd like to see higher-priority Item.C's at the top of the list, but I want to see all Item.C's with the same Type.B and Type.A grouped together. Some Type.B values appear across different Type.A's, but I only want to organize by priority within each Type.A, not across them. To do this, I built a calculated column that determines for each Type.B/Type.A combination what the lowest ranking is on the associated Item.C's. I want to sort Type.B by this calculated column, but when I try to use Sort by Column, I get the error message that we can't sort by that column because it has more than one value for the same value in Type.B because priority can differ across different Type.A's.

The only ways I've thought of to bypass this limitation is to somehow add a more customized code for sorting regardless of what the rows are that's displayed (no clue how to do this), or to add hidden characters based on the Type.A values to the end of the Type.B values so that Power BI thinks they're different values but they display the same way to end users. Is that possible?

Sample data to clarify:

Current result:

Expected result (partial anyway, but should convey the idea):

[v-eachen-msft]

Hi @ivan_larson_cki ,

You can create a new column to rank.

Column =
VALUE ( RIGHT ( 'Table'[Type.A], 3 ) ) * 1000000 + 'Table'[MIN RANK] * 1000 + 'Table'[Rank]

Here is the result.

[ivan_larson_cki]

Thanks. I've built out this column, but I'm still getting the error mesasge "There can't be more than one value in 'Column' for the same value in 'Type.B'. Please choose a different column for sorting or update the data in 'Column'."

[v-eachen-msft · Accepted solution]

Hi @ivan_larson_cki ,

You can consider to create a custom sort table and link to the raw table, then use related sort table fields as sort order:

Custom Sorting in Power BI

[ivan_larson_cki]

Thanks. I combined your advice with the use of hidden characters (added conditionally based on the Type.A value) to distinguish Type.B values by issue area, and was able to sort the table as needed.