How to have different format for specific values in a column in power bi?

Question

I have a target/goal column that needs to show different numerical formats in the same column in the table visualization. I also have a FormatType column which tells what should be the format of the goal column. Below is the example of what the values of the goal column look like and what is desired format I need them to be in.

Goal    FormatType  Desired Goal Format (Goal2)
0.6     %           60%
33000   $           $33,000
0.0375  %           3.75%

I used the below code to create a new calculated column in DAX but my formatting is still not like I wanted

Goal2 = IF([FormatType] = "%", FORMAT([Goal], "Percent"), FORMAT([Goal], "$##,###"))

Goal2
60.0%
$33,000
3.7%

Also when I do this formatting the Goal2 column become Text datatype column instead of fixed decimal type column.

Any help will be appreciated.

FYI, it's currently impossible to have different formats for the same measure/column while keeping the data type numerical rather than text. — Alexis Olson

ptfaferreira ptfaferreira · Accepted Answer · 2018-09-30T23:49:28

The format you desired can be achieved with this formula:

Goal2 = IF([FormatType] = "%",FORMAT([Goal],"0.0%"), FORMAT([Goal], "$##,###"))

The issue of the Text datatype is because the pre defined "%" with a number can´t be converted. You can't avoid the "%" symbol in this phase?

How to have different format for specific values in a column in power bi?

1 Answers