X Axis numerical values sorting out of order

[jdubs]

In a column chart I have numerical values (representing location codes) ranging from 00 to 100. It is sorting the list ascending from left to right except it is placing 100 in between 09 and 11. It is as if it's ignoring the 3rd digit and treating it as 10. 

[v-xulin-mstf]

Hi @jdubs,

Is your issue solved?

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[v-xulin-mstf · Accepted solution]

Hi @jdubs

You can creat a seperate sorting table.

For text values that are not regular, you can define their ordering in the table.

If you still have some question, please don't hesitate to let me known.‌‌

Sample data and expected output would help tremendously.

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[jdubs]

Yes, creating the seperate sorting table did the trick. Thanks!

[jdubs]

I tried using the formula in this post (https://community.powerbi.com/t5/Desktop/Number-sorting-in-text-format/m-p/237256) but was getting circular dependency errors. Creating a seperate Sorting table seemed to do the trick: (https://endjin.com/blog/2020/07/quick-tip-updating-the-sort-order-of-a-column-in-power-bi)

[jdubs]

I should mention that the Data Type for these values is Text. If I change it to whole number I get huge gaps in my chart where non-existent location #s don't have any corresponding data.