Forum Discussion

Helper I

9 months ago

Solved

Avoid scientific notation in PowerBI

I want small values in my Power BI reports to display as plain zeros instead of using scientific notation like “2E+10.”

Hans-Georg_Puls
9 months ago
If you want a more dynamic solution or different formats at different places you can define a measure using the FORMAT function:
Some details about the FORMAT function
Simple measure could be (first section is for positive number, second for negative number, third for zeros)
Simple Formatted Value = FORMAT(<Your Number as field or measure>, "#,##0.00;-#,##0.00;0")
See above documentation for details.
An example for a more advanced measure would be:
Advanced Formatted Value =
VAR _ValueAbs = ABS([Population Male Count Invers])
VAR _Format = SWITCH(
TRUE(),
_ValueAbs <= 1E3, "#,0.00;-#,0.00",
_ValueAbs <= 1E6, "#,0,.00 K;-#,0,.00 K",
_ValueAbs <= 1E9, "#,0,,.00 M;-#,0,,.00 M",
"#,0,,,.00 B;-#,0,,,.00 B"
)
RETURN
IF(
NOT ISBLANK([Population Male Count Invers]),
FORMAT([Population Male Count Invers], _Format)
)

It depends on your needs....

Super User

9 months ago

To avoid scientific notation like "2E+10" in Power BI and show small numbers as plain zeros, use a custom format string for your field or measure. In the Modeling view, select your field, then set a custom format such as 0;-0;0, "0" or #,0;#,0;0 .this ensures that very small values display as zero instead of scientific notation.

Steps to Apply This

Go to the Modeling view in Power BI Desktop.
Click on your measure or column.
In the Properties pane, set the "Format" to "Custom".
Enter your desired custom string (e.g., 0;-0;0 or 0;-0;"0").

This will display all zero and very small values as '0' in your visuals, completely eliminating scientific notation from your reports.

Related content