FORMAT function on Negative Numbers

[PowerBIET]

Why are the negative values blank when using my [Revenue Formatted] measure?  I was expecting the FORMAT to convert into a parenthesis (negative number) based on the formats I applied in the SWITCH function.  Please help me resolve. 

OneDrive Link to PBIX: SampleProject.pbix

Revenue Formatted =

VAR sales = [.Revenue]

VAR result =

SWITCH(TRUE(),

        sales > 1000000,FORMAT(DIVIDE(sales,1000000,0),"$#.#;($#.#)"),

        sales > 100000,FORMAT(ROUND(DIVIDE(sales,1000000,0),3),"$0.###;($0.###)"),

        sales > 10000,FORMAT(ROUND(DIVIDE(sales,100000,0),2),"$0.###;($0.##0)"),

        sales > 1000,FORMAT(ROUND(DIVIDE(sales,10000,0),1),"$#.#00;($#.#00)"),

        sales = 0.00,0

    )

RETURN

result

[rajendraongole1 · Accepted solution]

Hi @PowerBIET - To handle negative values, you need to add a condition for sales < 0 or rework the logic so negative values fall through to the appropriate FORMAT string. Here's the corrected measure:

VAR sales = [.Revenue]

VAR result =

    SWITCH(

        TRUE(),

        sales > 1000000, FORMAT(DIVIDE(sales, 1000000, 0), "$#.#;($#.#)"),

        sales > 100000, FORMAT(ROUND(DIVIDE(sales, 1000000, 0), 3), "$0.###;($0.###)"),

        sales > 10000, FORMAT(ROUND(DIVIDE(sales, 100000, 0), 2), "$0.##0;($0.##0)"),

        sales > 1000, FORMAT(ROUND(DIVIDE(sales, 10000, 0), 1), "$#.#00;($#.#00)"),

        sales = 0, "0",

        sales < 0, FORMAT(sales, "$#,##0.00;($#,##0.00)")

    )

RETURN

Please find the attached pbix FYR