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 p...
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.

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper 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
    Icon for Super User rankSuper 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.