Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

formatting numbers

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?
Need Help

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

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

      • V-lianl-msft's avatar
        V-lianl-msft
        Community Support
        4 years ago

        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 ()
    )