Forum Discussion

ChrisPBI's avatar
ChrisPBI
Advocate III
10 years ago
Solved

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...
Need Help
Eric_Zhang's avatar
Eric_Zhang
Microsoft Employee
10 years ago

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.

 

ChrisPBI's avatar
ChrisPBI
Advocate III
10 years ago

Hi Eric,

 

no idea how to upload the Excel her ..

 

Regards,

Chris

  • CahabaData's avatar
    CahabaData
    Memorable Member
    10 years ago

    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..... 

    • ChrisPBI's avatar
      ChrisPBI
      Advocate III
      10 years ago

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

      • sdjensen's avatar
        sdjensen
        Solution Sage
        10 years ago

        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.

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    10 years ago

    ChrisPBI

     

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

      • CahabaData's avatar
        CahabaData
        Memorable Member
        10 years ago

        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