Forum Discussion

jmuthukumaran's avatar
jmuthukumaran
Frequent Visitor
4 years ago
Solved

Multiple formats in a single column

Team , 

 

I am getting data as below from excel , but the challenge is "order, Ship and cuts" rows are in number format where as CFR(cars) , CFR(Trucks) and Target % rows are in percentage, Is it possible to have the same in Power bi desktop after consuming the excel.

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi jmuthukumaran ,

     

    In Power BI, if a column contains number and and percentage, I think the type of the column would be text format.

    This is not recommended at all. Please use seperate columns.

     

    Best Regards,

    Stephen Tao

     

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

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    I am not sure how your data model looks like, but I tried to create a sample pbix file like below.

    I hope the below can give some ideas on how to create a measure and apply it to your data model.

    Please check the below picture and the attached pbix file.

     

     

    Expected outcome measure: =
IF (
    HASONEVALUE ( Category[Category] ),
    SWITCH (
        SELECTEDVALUE ( Category[Category] ),
        "CFR",
            FORMAT (
                DIVIDE (
                    CALCULATE ( SUM ( Data[Value] ), Category[Category] = "ship" ),
                    CALCULATE ( SUM ( Data[Value] ), Category[Category] = "order" )
                ),
                "#0.00%"
            ),
        SUM ( Data[Value] )
    )
)
    • jmuthukumaran's avatar
      jmuthukumaran
      Frequent Visitor
      4 years ago

      Thanks for the reply , in my case i have months (Jan , Feb etc ) in columns do i need to create measures for each month separately or with 1 measure it will get applied across months 

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        4 years ago

        Hi,

        Thank you for your message.
        Without seeing your data model, it is quite difficult for me to tell the answer, but in my opinion, in general situation, one measure will work fine.

        Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi jmuthukumaran ,

     

    In Power BI, if a column contains number and and percentage, I think the type of the column would be text format.

    This is not recommended at all. Please use seperate columns.

     

    Best Regards,

    Stephen Tao

     

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