Forum Discussion

SYM1986's avatar
SYM1986
Icon for Helper I rankHelper I
5 years ago
Solved

how to keep two formats in one column.

KPI

Value
Turnover rate3%
NC rate1%

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

General Comment
Need Help
  • v-cazheng-msft's avatar
    v-cazheng-msft
    5 years ago

    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.

6 Replies

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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's avatar
      SalvadorOchoa
      Regular Visitor
      2 years ago

      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! 

       

       

       

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
    PC2790
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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