conditional different number formatting based on number length

[Eyal]

Hi all

I have a table that have number ranging from 2 digit after the decimal (0.xx) point up to tens of thousends (10,000).

I would like to format the Values such that:

(1) values up to 500 will be displyed with a format of 2 decinal point (e.g. 111.22 / 0.22).

(2) values abouve 500 will have a K symbole (e.g 500=>0.5K / 5000=>5K / 50000=>50K)

 any ideas on how to do that?

thanks 

Eyal

[Anonymous · Accepted solution]

Hi  @Eyal ,

Create a measure as below:

Measure = IF(MAX('Table'[Column1])<500,FORMAT(MAX('Table'[Column1])/1000,"fixed"),CONCATENATE(MAX('Table'[Column1])/1000,"k"))

And you will see:

For the related .pbix file,pls click here.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Eyal]

Thank you @Anonymous 

the solution you provieded is as close as i can get. to only issue is that then the colmun is defined as text (not number).

thank you all for your support

Eyal

[amitchandak]

@Eyal , I doubt that is possible. You can do using formatting string, but that will make it as string

You can use swith and the format you want

https://docs.microsoft.com/en-us/power-bi/desktop-custom-format-strings