Help, Stuck with Text Column (How to display decimal as a percentage?)

[gordgord1]

Hello, 

Dealing with people who mixes data types. Causing a hassle for me.
Need to call in someone smarter than me. 
Stuck using a text column. Most people want to see percentages in this format 75% not 0.75.

ColumnA (Text)	New Column (Text)
	blank
0	0
0.05	0.05
0.303191489	0.303191489
0.333333333	0.333333333
1	1
N/A	N/A

New Column = SWITCH (

    TRUE (),

    DataSource[ColumnA] = "N/A", "N/A",

    ISBLANK[DataSource[ColumnA]) = TRUE, "blank",

    ISBLANK(DataSource[ColumnA]) <> TRUE, FORMAT(DataSource[ColumnA],"Percent"), "otherwise")

(Note: inserted word "Blank", just to confirm that portion was working).

I also tried. ISBLANK(StackFEDEDVPData[% with DV Requests]) <> TRUE, FORMAT ( StackFEDEDVPData[% with DV Requests], "#.00%" ).

Nothing I do is working. Don't know if hitting tool limitations. Thanks in advance. 

Kevin

[gordgord1]

@PhilipTreacy  I work with engineers, not data scientists. Stuck with what is being handed to me. Hence, mixed data in a column. For sake of simplicity, backed off desired format. Not worth the effort or complexity. Just used a formula to standardize the data in the text column so decimal places were no longer variable. 🙂

Prefer not to needlessly complex formula. 

Cleaned Up Column = LEFT(DataSource[MyColumnName],4).

Employed a different tactic for color background. Some days not worth the effort fighting a poor data structure.

Closing Request for Help.

[PhilipTreacy · Accepted solution]

Hi @gordgord1 

Download example PBIX file with the code below

Do you need to keep the N/A ?

Wouldn't getting rid of text and making the column numeric be more useful for further analysis and calculation?

Use this to create the column

Column = 

SWITCH( TRUE(),

[ColumnA (Text)] = "N/A", BLANK(),

[ColumnA (Text)] = "", BLANK(),

VALUE([ColumnA (Text)]
))

Then format the column as Percentage

Or if you want a measure

Measure = 

VAR _value = SELECTEDVALUE(Data[ColumnA (Text)])

RETURN 

SWITCH( TRUE(),

_value = "N/A", BLANK(),

_value = "", BLANK(),

VALUE(_value)
)

Format the measure as a Percentage.

If you want to keep the column as text then use this

Column 2 = 

SWITCH( TRUE(),

IFERROR(VALUE([ColumnA (Text)]), TRUE()), [ColumnA (Text)],

FORMAT(VALUE([ColumnA (Text)]), "0.00%")

)

Regards

Phil

[gordgord1]

@Idrissshatila Thanks I will give a try. Almost inclined to say deal with the numbers as is for sake of simplicity.

[Idrissshatila]

Hello @gordgord1 ,

check if the following works for you 

Formatted Percentage Column = 
VAR txt = TRIM( 'Table (2)'[ColumnA (Text)] )
VAR txtClean = SUBSTITUTE(txt, ",", ".")
VAR numFromPercent =
    IF(
        CONTAINSSTRING(txtClean, "%"),
        IFERROR( VALUE( SUBSTITUTE(txtClean, "%", "" ) ) / 100, BLANK() ),
        BLANK()
    )
VAR numFromValue = IFERROR( VALUE(txtClean), BLANK() )
VAR num = COALESCE( numFromValue, numFromPercent )
RETURN
IF(
    ISBLANK(num),
    txt,
    FORMAT(num, "0.00%")
)