Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Vind1989
Helper III
Helper III

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?
6 REPLIES 6
AlexisOlson
Super User
Super User

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

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

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

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

amitchandak
Super User
Super User

@Vind1989 , 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

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.