Detecting the format of the text and reformatting it when true

[Anonymous]

I have a problem where when the value returned by a measure is to be formatted 

1. if it is =100.0 then format to ###

2. if the values are like 49.0% then I want only 49% but what my below measure does it, gives 49.% 

3. if blank then 0%

New = if([Score]=blank(),"0%",IF([Score]=1.0,FORMAT([Score]*100,"###")&"%",FORMAT([Score ]*100,"###.0")&"%"))))

Note : [Score] is a measure too

Thanks

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

Create a measure as below:

Measure = 
var _check=RIGHT('Table'[Score],1)
var _search=SEARCH(".",'Table'[Score],1,0)
Return
IF('Table'[Score]=BLANK(),"0%",IF(_check="%",LEFT('Table'[Score],_search-1)&"%",LEFT('Table'[Score],_search-1)))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[amitchandak]

@Anonymous , what issue you are facing in this , With small change try

if( isblnak([Score]),"0%",IF([Score]=1.0,FORMAT([Score]*100,"###")&"%",FORMAT([Score ]*100,"###.0")&"%"))))

[Anonymous]

thanks for responding @amitchandak but the solution I am looking for is, if my score measure is returning 49.0% then my mesure new should not return the decimal place, so I want to detect if there is a 0 in the first  decimal place, id so then just show ### alone and not ###.0

[amitchandak]

@Anonymous , is it for all complete numbers or only 49 

if( isblnak([Score]),"0%",IF([Score]=1.0 || [Score]=.49,FORMAT([Score]*100,"###")&"%",FORMAT([Score ]*100,"###.0")&"%"))))

[Anonymous]

all numbers like that, basically something which has .0 in the decimal place then return whatever is before the decimal place @amitchandak 

[v-kelly-msft]

Hi  @Anonymous,

Based on my understanding,you only need to adjust the measure format under "measure tools" to change the number of decimal places shown for this value as 0.

Best Regards,
Kelly

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