how to keep two formats in one column.

[SYM1986]

KPI	Value
Turnover rate	3%
NC rate	1%
Transportation cost	1.73
operation cost	2.2

Hi all,

I have a report to finish with using power bi, the data source is silimarl like above.  The column of value contain two format, percentage and decimal,  and i want to keep both the formats for creating report.

Can anyone help me find how to keep the two formats? because now the power bi covert them into deical.

Thanks a lot

[v-cazheng-msft · Accepted solution]

Hi, @SYM1986 

You can create a Measure with CONTAINSSTRING and FORMAT function. However, the data type of values in this Measure is Text. As @CNENFRNL  said, there is only one data type for one column.

TwoFormatsValue = IF(CONTAINSSTRING(SELECTEDVALUE('Table'[KPI]),"rate"),FORMAT(SELECTEDVALUE('Table'[Value]),"#%"),SELECTEDVALUE('Table'[Value]))

The result looks like this:

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SalvadorOchoa]

Hello!! I got the same situation but i want to do a line chart of these 2 values ( Numebrs and %)

The % are not visable but the number we can see them. Please help! 

[SYM1986]

@v-cazheng-msft  thank you,  you gave a very subtle solution.

[CNENFRNL]

@SYM1986 , up till the latest version of PBI, only one data format can be defined for one column.

As to your issue, there's a workaround like this,

1. use different measure in a matrix viz, i.e. Turnover rate, NC rate, Transportation cost etc,

2. format the matrix: Format roller - Values - show on rows

voilà, the matrix changes its layout as expected.

[PC2790]

Hi,

I am afraid it is not possible to do in Power BI(Pivot & Query). One column should only have one type. In case we have different data types like numbers and text, it is recommended that we set column type as Text

However, I was able to find a workaround for this here.

See if it works for you.

Please provide a Kudos to this answer if you found it interesting.

If this post helps, then please consider accepting it as the solution to help the other members find it more quickly

[SYM1986]

@PC2790 

Thank you