Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ChrisPBI
Advocate II
Advocate II

Values with many zeros

Hello everyone,

 

I connected Power BI with an Excel spreadsheet.

 

When I display the values, strange numbers after the decimal point occur.

 

E.g. in Excel a sum is 800, but Power BI shows it as 800,0000000001.

 

Does enybody know this problem?

 

Thanks and Regards,

Chris

1 ACCEPTED SOLUTION

@CahabaData

 

In my opinion, it is actually a develoment item regarding "float is approximate". Check Floating point inaccuracy examples and Floating point.

 

@ChrisPBI

Thanks for sending me the pbix. In this case, try to change the "GGE in kg CO2e" from "decimal number" type to "fixed decimal number". Check the difference in Data types in Power BI Desktop.

 

View solution in original post

11 REPLIES 11
Eric_Zhang
Microsoft Employee
Microsoft Employee

@ChrisPBI

 

I can't reproducte it in my test.

 

What does the data look like in table? I also see ","(comma) as a decimal point, is there any specific locale configuration in your case? Can you upload the excel for test purpose? Do mask sensitive data before uploading.

 

Hi Eric,

 

no idea how to upload the Excel her ..

 

Regards,

Chris

@ChrisPBI

 

You can use any web storage, such as onedrive, dropbox. Please upload the pbix along with the Excel file.

@CahabaData

 

In my opinion, it is actually a develoment item regarding "float is approximate". Check Floating point inaccuracy examples and Floating point.

 

@ChrisPBI

Thanks for sending me the pbix. In this case, try to change the "GGE in kg CO2e" from "decimal number" type to "fixed decimal number". Check the difference in Data types in Power BI Desktop.

 

I believe my prior post is not correct.  One is able to control the precision / decimal place count in a visual.  I believe this could be the original post question.

 

Highlight the table/field on the right most Field page that has too many decimal places, then go to the ribbon's Modeling tab - and up there change the Decimal Number default from 'Auto' to 0

 

 

www.CahabaData.com

I think M.A. is right about rounding.  In my experience what displays and what is actual does vary. 

 

Depending on where these values come from - if they are calculated then the round function in the Dax/SQL is key...but if they are from your source then you have a couple options:

 *Are you sure in excel that you have explicitly formatted the column? - do that.

 *Then in Power BI, in the data setting of the table/query - format it to 2 decimal places there too....

 

Of course if it is your source; and if you are going to be receiving new data via excel repeatedly then reformatting that column each time is a bit of a hassle so hopefully the Power BI setting will suffice..... 

www.CahabaData.com

I formated everything to numbers with to numbers behind the decimal point. Nothing helped ..

@ChrisPBI - what is the base datatype of your columns? The seem to be some rounding issues if you use Decimal Number datatype - I always set the datatype of my source to Fixed Decimal Number and then when creating measures I change the datatype back to Decimal Number.

/sdjensen

So I can reproduce this.  If one uses one of the chart visuals, using a number field - that is then optioned for the average (the default is typically the sum) - - the resulting value when you hover your cursor over a spot - - I am seeing many decimal places.  And I don't see any way to limit that.

 

I happen to use a line chart but am assuming any chart with a number that gets optioned to be calculated as part of the visual - - I am not seeing any option to control that number format.....

 

It is kind of crazy to see:    23.33333333333333   so unless I am missing an aspect in the existing interface this probably should be a development item.....

www.CahabaData.com
MattAllington
Community Champion
Community Champion

your data looks like it is not rounded.  Try formatting the number with 1 or zero decimals 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

Hi,

 

my values are rounded to two numbers after the decimal point.

 

Such as:

25,65
15,30
3,60
0,45

 

But this results in Power BI in:

 

Bild1.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I have no idea why.

 

Thanks for helping.

 

Regards,

Chris

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.