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:
When you load this data into Power BI data model, it appears as below:
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:
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!!
Now go back to Report view and the visual now looks as expected like the below:
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