Multiple formats in a single column

[jmuthukumaran]

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.

[Anonymous · Accepted solution]

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]

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]

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]

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.