Getting rid of scientific format for decimal numbers in Power BI

If you have ever dealt with decimal numbers with too many decimal places and expose such data in Power BI, you might see them getting automatically formatted in scientific notation.

For example, see the below sample data from a csv file:

Targets for different teams in a company

When you load this data into Power BI data model, it appears as below:

Sample team targets data after populating into Power BI data model

Now see that the target for the “Marketing” team which was 0.00002 (decimal format) is appearing as 2E-05 (scientific format). and when you try visualizing too (say in a simple column chart) the format still appears in scientific as below:

Decimal number auto-converted to scientific format

A lot of community users have proposed solutions in power query and DAX that doesn’t seem to be working. The solution is actually lot more simpler. You might even find the solution to be straight forward if you are an experienced Excel user. To fix this to show the team’s targets as decimals and not converted to scientific format is to customize the display format of this column or measure to #.###########; 0; #.########### in the model view as shown below. That works the magic!!

Updating the format option to Custom and custom format option entered as #.###########;0;#.###########

Now go back to Report view and the visual now looks as expected like the below:

Team targets now displayed in decimal format without converting them to scientific display format

By the way, # in custom format implies a number and you were telling the Power BI about how to display positive, zero and negative numbers. The formats for positive, zero and negative numbers are separated by semi-colon.

Hope that was a learning for the day. See you soon with another learning write-up.

Leave a Reply

Up ↑

%d bloggers like this: