DAX to sort numbers stored as text in a measure inside a matrix

[Anonymous]

Hi all,

Having a bit of trouble sorting numbers stored as text. I'm using a matrix to display the values, but because the values are big (in billions and trillions), I used dax to format the numbers with the FORMAT function, but since this becomes a text measure, I'm trying to sort them as if they were numbers. I cannot use the format options on the matrix/specific columns because it doesn't have the "Auto" feature, I need to display numbers in thousands, millions, billions and trillions accordingly.

I have found resources online that showed using RANKX along with the REPT function to repeat a non-width character UNICHAR(8203) before the format (for example "$0.00"), but my rankx function is not working quite well.

I'm attaching a .pbix file for you to consider, any help is appreciated, thanks! 

pbix file 

[Greg_Deckler]

@Anonymous Try using a Custom Format String instead of the FORMAT function. This leaves the values as numbers but displays them in the format you choose. Use custom format strings in Power BI Desktop - Power BI | Microsoft Learn

[Anonymous]

Bumping this up

[Anonymous]

Hi Greg, I did try that, doesn't really work in my situation though. The 3 sections of a custom format is positive values;negative values;zero values formatting...I tried excel style formatting for values under a certain threshold to display millions and billions but sadly didn't work out.

[Greg_Deckler]

@Anonymous Can't you just RANKX based on the numeric column but use the FORMAT column in your visual?

[Anonymous]

That is exactly what I tried to do, see here: