Forum Discussion

JamesBurke's avatar
JamesBurke
Helper III
1 year ago
Solved

Formatting issue

Hi All , 

 

Created a custom column using format

 

 

Rounded data = 
VAR _Var = 'Emporia Device Usage'[Usages KWH]
    RETURN
    SWITCH(
        TRUE(),
         _Var < 0.01  , FORMAT('Emporia Device Usage'[Usages KWH], "0"),
         _Var < 1 , FORMAT('Emporia Device Usage'[Usages KWH], "0.##"),
        _Var < 10 , FORMAT('Emporia Device Usage'[Usages KWH], "##.##"),
        _Var < 100, FORMAT('Emporia Device Usage'[Usages KWH],"##.#"),
        _Var > 100 ,Format('Emporia Device Usage'[Usages KWH],"###"))

 

But recieve errors like below 

 

 

Thanks , James. 

Need Help
  • shafiz_p's avatar
    shafiz_p
    1 year ago

    Hi JamesBurke The Format function in Power BI DAX converts the data type to a string. This function is used to format a value as a string according to a specified format. Your measure [Total Kwh] most problably summing up your calculated column which is techincally not possible.

    To overcome this situation, use sumx and value function to evaluate sum. For example:

     

    Total = SUMX(
    orders,
    VALUE(orders[StringOrder])
)

     

    Here orders[StringOrder] column have used format function and data type is text.

     


    Hope this helps!!

    If this solved your problem, please accept it as a solution!!

     

     

    Best Regards,
    Shahariar Hafiz

2 Replies

  • shafiz_p's avatar
    shafiz_p
    Super User
    1 year ago

    Hi JamesBurke The Format function in Power BI DAX converts the data type to a string. This function is used to format a value as a string according to a specified format. Your measure [Total Kwh] most problably summing up your calculated column which is techincally not possible.

    To overcome this situation, use sumx and value function to evaluate sum. For example:

     

    Total = SUMX(
    orders,
    VALUE(orders[StringOrder])
)

     

    Here orders[StringOrder] column have used format function and data type is text.

     


    Hope this helps!!

    If this solved your problem, please accept it as a solution!!

     

     

    Best Regards,
    Shahariar Hafiz