My custom format order is wrong

[Tenuki]

Hello, 

I create a custom format for my revenue, but the order in value is wrong.

For instance, I get this table :

CatID     Revenue

5            266.2 K €

1            2.1 M € 

3            1.1 B €

How to order it by billion, million, thousands and then order by values ?

[Or]

Don't use text in your numbers... use formatting functions, specifically num(), and/or the dual() function. 

[Tenuki]

This my expression, then I use auto format. Should I use numeric instead ? 

If(sum(Revenue)/1000000000 > 1,
Num(sum(Revenue)/1000000000,'### B €'),

if(sum(Revenue)/1000000>1,
Num(sum(Revenue)/1000000,'### M €')
,

if(sum(Revenue)/1000>1,
Num(sum(Revenue)/1000,'### K €')

)))

[Or · Accepted solution]

Don't change the underlying number, which is what you're doing when you divide it by different figures.

You can use Dual here, for example:

Dual(Num(sum(Revenue)/1000,'### K €'),Sum(Revenue))

This will maintain the correct sum(revenue) while keeping your format as the text representation. 

[anat]

you may try using custom expression in sorting

sum(Revenue) then select numeric desc order

[Tenuki]

Many thanks 🙂