Formatting issue

[JamesBurke]

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. 

[shafiz_p · Accepted solution]

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

[Tahreem24]

@JamesBurke  What's the data type of 'Emporia Device Usage'[Usages KWH] column?