Custom Sort on an axis -- but with one caveat

[bvy]

Hi. I want to do a custom sort on the x axis of a Line Chart. Consider this scenario: Salespeople visit a group of cities -- Atlanta, Boston, Chicago, Dallas, etc. and I want to display these cities on the x axis but in the order that the salespeople typically visit them. 

So if I have a slicer for salesperson and I select ANN, who visits Boston then Atlanta then Dallas. So for Ann, I want those cities in that order on the x axis. But BOB visits Atlanta then Chicago then Boston, so if I select Bob, I would want to see those cities in that order instead. 

I know I can configure a Sort By column and make it the default sort for some other field, but it requires a UNIQUE numerical assignment for each value. Here that's the city name and that won't work. 

I do NOT want to concatenate the salesperson's name to the city to put in the x axis. That will fix the issue but at the expense of cluttering up the x axis with redundant information, so that's not an option. The x axis should ONLY show the city name.

Any ideas? Thanks. 

[jdbuchanan71]

Yep, it's the only way to get access to the value to use it for sorting.  Maybe someday they will add a sorting selection option.

[bvy]

Thanks. That works pretty well. I'd rather not have to carry around that value in the tooltip, but it's an acceptable compromise. 

[jdbuchanan71 · Accepted solution]

One way to do it would be to add a table that has the Rep, City, and Order for each then use that in a measure to calculate the order value, apply that measure in the tooltip so it can be used in the sort then sort the chart by the measure.

RepCityOrder table looks like this.

This table is NOT joined into your main data table.  We use TREATAS to move the filters when we are calculating the order.

City Order =
CALCULATE (
    MAX ( RepCityOrder[Order] ),
    TREATAS (
        SELECTCOLUMNS ( Sales, "Rep", Sales[Rep], "City", Sales[City] ),
        RepCityOrder[Rep],
        RepCityOrder[City]
    )
)

Then we put that measure in the tooltip of the visual, this allows us to sort by that measure,

This gives use the behavior you describe.

I have attached my sample file for you to look at.