Forum Discussion
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 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
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
4 Replies
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.
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%") )Idrissshatila Thanks I will give a try. Almost inclined to say deal with the numbers as is for sake of simplicity.
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
