Custom Sort Help Needed

[dawaro]

I am trying to build a table visual with two columns from my dataset, Priority and Actual Hours with a measure to calculate the percentage of Actual Hours/Total Hours for each priority.

What I need is to be able to custom sort the Priority column because their severity does not match their alphabetic order.

The order should be:

Priority	Actual Hours	Percentage of Hours
Emergency	22.5	5.2%
Schedule Break-In	78.0	18.1%
Routine - High	173.0	40.2%
Routine - Medium	15.0	3.5%
Routine - Low	142.0	40.2%
Total	430.5	100.0%

Emergency

Schedule Break-In

Routine - High

Routine - Medium

Routine - Low

I have tried to accomplish this with sort tables and sort columns as well as different measures but everything changes the Percentage of Hours to 100%.

What am I doing wrong?

[dawaro]

Thank you that worked perfectly

[v-kathullac · Accepted solution]

Hi @dawaro ,

Thank you for reaching out to Microsoft Fabric Community Forum.

can you follow the below steps and let us know if you need any assistance.

1)Create a separate table (manually or with DAX) to define your custom priority order:
PrioritySort = DATATABLE(
"Priority", STRING,
"SortOrder", INTEGER,
{
{"Emergency", 1},
{"Schedule Break-In", 2},
{"Routine - High", 3},
{"Routine - Medium", 4},
{"Routine - Low", 5}
}
)

2)In the Model view, create a relationship between your main data table's Priority column and the new sort table’s Priority.

3)Alternatively, merge the SortOrder column into your main data table in Power Query.

4)In the main data table, select the Priority column → click "Sort by Column" → choose SortOrder.

5)Create a measure for Total Actual Hours:
Total Actual Hours = SUM('Test'[Actual Hours])

6)Create a measure for Percentage of Hours:
Percentage of Hours = DIVIDE(SUM('Test'[Actual Hours]), CALCULATE(SUM('Test'[Actual Hours]), ALL('Test')))

7)Add a table visual with Priority, Actual Hours, and Percentage of Hours.

8)Ensure the table respects custom sort order and shows correct percentages.

Regards,

Chaithanya.

[ExcelMonke]

What does your percentage measure look like?

[dawaro]

This is the measure I am using to calculate the percentage of hours.

Percentage of Hours = DIVIDE(SUM('TestFile'[ACTUAL HOURS]),CALCULATE(SUM('TestFile'[ACTUAL HOURS]),ALL('TestFile'[PRIORITY])),0)

[dawaro]

I have tried that and while it does correct the order it also changes the percentage for each row to 100%.

Here is the model:

Here is the result:

[ExcelMonke]

Hello, 

Sorting you columns can sometimes be tricky! My recommendation for you to consider is to create a secondary table for sorting purposes. It needs to only contain two columns like so:

Priority	Sort Order
Emergency	1
Schedule Break-In	2
Routine - High	3
Routine - Medium	4
Routine - Low	5

From there, connect this table in your data model to your main fact table. You can then use the Priority column from this new table in your matrix, and sort by the table's sort order column. 

It's a bit convoluted, but it is a way to make it work!