Keeping Sort after Auto Unit Change Dax Formatting

[PaulBrumby]

Hi All, 

I have the following which auto changes the units on some matrix visuals when the report being filtered.

Sales£ =

VAR Tot = Sum('Prod FactSales'[Amount Home])

RETURN

IF(ABS(Tot)>1000000000,

          FORMAT(Tot, "£#,,,.0bn"),

IF(ABS(Tot)>1000000,

          FORMAT(Tot, "£#,,.0M"),

           IF(ABS(Tot)>1000,

             FORMAT(Tot,"£#,.0K"),

          FORMAT(Tot, "£#,0")

             )

          ))

However........I am unable to keep my sort by that column, is there a way I can always sort descending by this column, even considering the formatting?

Many Thanks

[vanessafvg · Accepted solution]

well you are changing it to a text value  so it will sort alphabetically if i understand you correctly.  Create 2 versions of the column one text and the other number and sort the text column by the number column