Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Formula conversion from excel to M

Hi everybody,   I need to translate the formula in the excel attached in Power BI.   Any ideas on how to do it?   Thanks in advance.   Regards, AS      
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Anonymous,

     

    Please new a calculated column with DAX formula:

    Ct. Converted =
FORMAT (
    VALUE (
        IF (
            ISERROR ( VALUE ( LEFT ( Sheet2[Ct. Range], 1 ) ) ),
            BLANK (),
            IF (
                VALUE ( LEFT ( Sheet2[Ct. Range], 1 ) ) = 0,
                "0." & MID ( Sheet2[Ct. Range], 3, 5 ),
                IF (
                    VALUE ( LEFT ( Sheet2[Ct. Range], 1 ) ) > 0,
                    LEFT ( Sheet2[Ct. Range], 1 ) & "."
                        & MID ( Sheet2[Ct. Range], 3, 5 )
                )
            )
        )
    ),
    "0.0000"
)

     

    Best regards,

    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi Anonymous,

 

Please new a calculated column with DAX formula:

Ct. Converted =
FORMAT (
    VALUE (
        IF (
            ISERROR ( VALUE ( LEFT ( Sheet2[Ct. Range], 1 ) ) ),
            BLANK (),
            IF (
                VALUE ( LEFT ( Sheet2[Ct. Range], 1 ) ) = 0,
                "0." & MID ( Sheet2[Ct. Range], 3, 5 ),
                IF (
                    VALUE ( LEFT ( Sheet2[Ct. Range], 1 ) ) > 0,
                    LEFT ( Sheet2[Ct. Range], 1 ) & "."
                        & MID ( Sheet2[Ct. Range], 3, 5 )
                )
            )
        )
    ),
    "0.0000"
)

 

Best regards,

Yuliana Gu

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Yuliana,

 

how can I convert the output of the formula you shared into numbers instead of text?

 

Thanks in advance.

 

AS