Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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
@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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |