Forum Discussion

gordgord1's avatar
gordgord1
Regular Visitor
9 months ago
Solved

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

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 7...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    9 months ago

    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

     

Idrissshatila's avatar
Idrissshatila
Super User
9 months ago

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%")
)