formatting numbers

[Anonymous]

Hi all,

I am using a table visual and I am trying to format all the numbers. I cannot use a matrix visual for this example (and use the tabular editor to format the numbers). here is the dax I am using to format the numbers:

SWITCH(

TRUE(),

[Metric] >= ( 10 ^ 8 ), FORMAT([Metric],"$#,0,,,.##B; ($#,0,,,.##B)"),

[Metric] >= ( 10 ^ 5 ), FORMAT([Metric],"$#,0,,.##M; ($#,0,,.##M)"),

[Metric] >= 1000, FORMAT([Metric] , "$#,0,.##K; ($#,0,.##K)"),

[Metric] < 1000 && [Metric] >= 0, FORMAT([Metric] ,"$#,##0.00;($#,##0.00)"),

[Metric] <= ( -10 ^ 8 ), FORMAT([Metric] , "$#,0,,,.##B; ($#,0,,,.##B)"),

[Metric] <= ( -10 ^ 5 ), FORMAT([Metric] ,"$#,0,,.##M; ($#,0,,.##M)"),

[Metric] <= -1000, FORMAT([DME ARR ETLA + VIP] ,"$#,0,.##K; ($#,0,.##K)"),

[Metric] < 0, FORMAT([Metric] ,"$#,##0.00;($#,##0.00)"),

BLANK()

)

the problem I am running into is locking conflicts & a terrible lag in rendering. Is there a better more efficent way to run this type of number formatting?

[AlexisOlson]

You can likely improve performance by calculating [Metric] once as a variable rather than recomputing it twice for each switch condition.

Formatting =
VAR Metric = [Metric]
RETURN
    SWITCH (
        TRUE (),
        Metric >= ( 10 ^ 8 ), FORMAT ( Metric, "$#,0,,,.##B; ($#,0,,,.##B)" ),
        Metric >= ( 10 ^ 5 ), FORMAT ( Metric, "$#,0,,.##M; ($#,0,,.##M)" ),
        Metric >= 1000, FORMAT ( Metric, "$#,0,.##K; ($#,0,.##K)" ),
        Metric < 1000 && Metric >= 0, FORMAT ( Metric, "$#,##0.00;($#,##0.00)" ),
        Metric <= ( -10 ^ 8 ), FORMAT ( Metric, "$#,0,,,.##B; ($#,0,,,.##B)" ),
        Metric <= ( -10 ^ 5 ), FORMAT ( Metric, "$#,0,,.##M; ($#,0,,.##M)" ),
        Metric <= -1000, FORMAT ( [DME ARR ETLA + VIP], "$#,0,.##K; ($#,0,.##K)" ),
        Metric < 0, FORMAT ( Metric, "$#,##0.00;($#,##0.00)" ),
        BLANK ()
    )

[Anonymous]

@AlexisOlson  this is also super helpful, thank you. Its still taking forever to render 

[V-lianl-msft]

Please try ABS function:

Formatting =
VAR Metric = [Metric]
RETURN
    SWITCH (
        TRUE (),
        ABS(Metric) >= ( 10 ^ 8 ), FORMAT ( Metric, "$#,0,,,.##B; ($#,0,,,.##B)" ),
        ABS(Metric) >= ( 10 ^ 5 )&&ABS(Metric) < ( 10 ^ 8 ), FORMAT ( Metric, "$#,0,,.##M; ($#,0,,.##M)" ),
        ABS(Metric)>= 1000&&ABS(Metric)< ( 10 ^ 5 ), FORMAT ( Metric, "$#,0,.##K; ($#,0,.##K)" ),
        ABS(Metric)< 1000 && ABS(Metric)>= 0, FORMAT ( Metric, "$#,##0.00;($#,##0.00)" ),
        BLANK ()
    )

[Anonymous]

@V-lianl-msft this is still causing the visual to keep trying to render but wont load 

[amitchandak]

@Anonymous , see you try something generic in data format under property in data model view, place of the format function

here -https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-custom-format-strings

[Anonymous]

@amitchandak  this is good info thanks, however I cannot run any logic for if the total is less then 10k I dont want it to populate at .0B