Unwanted Rounding Issue

[jarnone]

Hoping someone can please help with this weird issue. I've got a large data set with many columns that include numbers with two decimals. They all import as expected except for one column that rounds to the nearest whole number. 

I've tried everything I know of. I adjusted the Rounding settings. I've marked the column as Decimal, Fixed Decimal, Currency... Nothing has worked. It rounds this column no matter what I do. I've tried adjusting the settings on the column in excel before importing to see if this works either. 

I'm stumped... 

[madalenapneves]

I had the exact same issue, and tried everything and beyond, checked everything, and nothing worked. This solution you came up with was a miracle and the only thing that worked. I created a second column in excel equal to the original one, and PBI worked just fine.

Thank you!

[jarnone]

So glad! What a weird issue - too bad MS doesn't work on fixing this. 

[Cornwalls]

Also having a weird rounding issue. My visual just will not show the actual value that it should be. It should show 0.5 but no matter what it stays at 1.0. Randomly it changed to 0.5 last night so thought it was fixed, then this morning it has gone back to 1.0.

Visual:

Imported Dataset (Sharepoint List) says 0.5:

The PBI says it correctly in the transform data:

I have tried creating a new column as suggested by the OP, that unfortunately didnt work in my case - is there any direct Formatting in the Visual itself to stop rounding numbers?

[jarnone]

This is the first time I've heard of anyone else having the same issue! It's a frustrating issue. I received a lot of advice, but it was all simplistic solutions that did not work. I'm convinced it's a Microsoft glitch that no one has resolved yet. It's not something you are doing wrong!

The data set I use has dozens of two-decimal columns, which when imported, do not round. There was always one consistently rounded column no matter what I tried.

My solution was a work-around. I added a column in the Excel data doc that equals the corresponding cells. This imports fine and has been working fine for months. It's not an ideal solution, but it works... 

Hope your issue raises enough concerns with Microsoft for them to actually research the issue instead of doling out obvious advice. 

[jarnone · Accepted solution]

UPDATE

So I found a workaround. Not an actual solution, but at least it worked... I created a new Column that equals the values of the Column with the rounding issue. The new Column did not round the values. 

So this workaround was successful. I'm still not happy with the bugginess and the inability to resolve it properly though. Stumped...

[Angelheart0604]

When you say you created a new column, how exactly did you do that please as there are several options. Basically my issue is that the source data film, the column has no default formatting set, so when it is pulling the data into  into Power Query, it is rounding up ans displaying my figures differently to the source data. Bit annoying as where it is 0, I want it to stay as 0 as per the source, not change it to 0.00 if I add decimal places. Any ideas? 

[jarnone]

Hi, yes happy to clarify. The data source is an Excel doc that I update each week. One of the columns (for some unknown reason) rounds when Power BI connects to the data. All the other columns are fine... So, in the Excel doc, I created a new column all the way to the right. I simply created a formula to equal the other column. For instance, Y2=K2, Y3=K3, etc... I attempted this after trying a million things in Power BI with calculated columns and other query options. Simply added a column in Excel. 

I'm hoping someday Microsoft sorts this out since it's all their Office products!

[grazitti_sapna]

Hi @jarnone, 
You can achieve it by many ways:- 
One of them mentioned by @vicky_ 

Rounding in the Source Data:

• Examine the source data in its original form (before importing into Power BI). If the data is already rounded in the source, Power BI will import it as such.

Check Data Type in Source:

• Ensure that the column in your source data (e.g., Excel file) is formatted as a decimal with two decimals. Excel, for instance, has its own cell formatting settings, and sometimes those settings can affect how data is imported into Power BI.

DAX Calculations:

• Check if there are any DAX calculations in your Power BI model that might be rounding the values. Look for ROUND or ROUNDUP/ROUNDDOWN functions in your measures.

Let me know if any one of these works.
Thanks 

[jarnone]

Yes, I've tried those configurations as well. Tried many different combinations, unfortunately none have worked. Thanks though. 

[vicky_]

As a sort of last-ditch effort, once the data has loaded into powerBI, try check that the format of the column is not set to whole number (and that the decimal places column is not 0)

[jarnone]

Thank you. Tried all of those possible options as well. I've tried all the possible decimal and percedntage options in the column settings. And I've also check to ensure there's no DAX rounding formula behind the scenes. 

I'm still stumped...