custom order for legend, sort using another column

[bdpaasch]

Greetings all,

This is yet another round of, "I'm trying to custom sort a legend".... Lots of those questions here, sorry.

Modeling off of Parker Stevens' video (@https://www.youtube.com/watch?v=4xapOrfLvbg ) and Microsoft's page (@

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column?tabs=powerbi-desktop)  on the subject, I'm trying to sort a list of clients by another column to force sort an order of a legend.

However, the Client list is a couple dozen names long AND I want them to sort in order of our work load, therefore, a dynamic sort. A simple, manual 1, 2, 3, 4... doesn't do the job.

I created a separate Client table (names obscured for obvious reasons):

The "Total Pick Records" is a calculated column via a measure:

The measure is a simple count of records from my primary data table (making sure one particular data field isn't filtered):

Some of the clients have no record values which will be the case from time-to-time. I then created a rank column using the Total Pick Records plus a little bit of alphabetical ordering to break ties.  ( @Fowmy  taught me that trick).

Now I have a rank column that will update based on client workload (and then alphabetically in the case of work-load ties). I want to use that column to sort my legend (highest work-load clients to lowest). (I'll throw in a "greater than zero" filter on the visual to knock out the clients that didn't give us work in the time period.)

But, when I go to the "Sort by column" option on the ribbon under Column Tools and select "Rank Value" for the ordering, I get this error box:

Anyone have any ideas?

Thanks!

[amitchandak · Accepted solution]

@bdpaasch , first of all, the sort column will not be dynamic, it will not use a measure. I think tying to create a column using a measure, that is giving an issue.

You can sort it on overall sales. Add a random number to make it different

[bdpaasch]

@amitchandak Thanks. Based on what you wrote, I threw away my manually created Client Table. I created a referenced table from my data table, grouped the new table by client name and counted the rows (records) to create the workload numbers. I sorted the new little grouped table by client alphabetically, and added an index column. I created one more column, Force Rank, by adding the record count plus the index (divided by 1000) to break any ties that might form. Linked the Client fields from the two tables, did Sort By Column on the Force Rank and all good now. Thanks.