Forum Discussion
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.”
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")RETURNIF(NOT ISBLANK([Population Male Count Invers]),FORMAT([Population Male Count Invers], _Format))It depends on your needs....
6 Replies
In the Modeling tab, under Formatting → Format, choose:
Decimal Number or Whole Number.
Adjust decimal places as needed.
When you select the data column the options to set the Format and Numer of Decimal Places are actually in the Column Tools tab
Regards
Phil
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")RETURNIF(NOT ISBLANK([Population Male Count Invers]),FORMAT([Population Male Count Invers], _Format))It depends on your needs....
Hi knut12212 ,
I would also take a moment to thank PhilipTreacy , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions
Hi knut12212 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions
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.
