Forum Discussion
Power Bi Rounding Decimal Numbers up, even when not formatted to do so
Hi Ereon ,
Thanks for the update and apologies for the delay.This issue seems to be related to the new Snowflake connector introduced in recent Power BI Desktop versions. When using DirectQuery, decimal values may appear rounded during aggregation even though the source data and preview show correct values.
To work around this, go to File > Options and settings > Options > Preview features, and uncheck “Use new Snowflake connector implementation”. Restart Power BI and reload the dataset.
Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to Accept as the solution to help the other members find it more quickly.
Thank you.
Hi Ereon ,
Thanks for posting in Microsoft Fabric Community.
Thanks again for sharing the screenshots and confirming the formatting settings. I was able to reproduce a similar scenario in my environment and found that when the Data Type is set to Whole number for the column in the Model view like below, it appears as .00.
But when i changed it to Decimal number
it's correctly shown like below
Please click refresh after changing the data type.
If unable to do this in Direct Query:
--Check the data type of the Total $ Sales column in your source system.
--If it's defined without decimal precision, consider updating it to a type that supports decimals - such as DECIMAL(12,2) or NUMERIC(12,2).
Once the column allows decimal precision at the source level, Power BI will reflect the correct values in your visuals.
Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to give a kudos and Accept as the solution to help the other members find it more quickly.
Thank you.
Please find the attached .pbix file.
The data comes in as a Decimal Number, which makes this so confusing for me. This is a DirectQuery due to the size of the data model.
And the tables show the decimal places correctly when I go to Transform Data.
Hi Ereon ,
Thanks for the update.
Since the column is already in decimal number format and the field shows correct values in both Snowflake and the Power Query preview, the issue could be related to how the values are being handled in the visual.
As a next step, try setting the column to "Don’t Summarize" in the table visual. This will ensure that the values are displayed directly without any implicit aggregation that might affect how they're shown.
You can also create a simple measure like MAX('Table'[Total $ Sales]) and add it to the same visual to see if it behaves any differently from the column.
Just as a general note, Power Query may sometimes infer data types based on the first few rows. If those happen to contain only whole numbers, it might initially assign the column a whole number type. You can check or change this by going to Transform Data, right-clicking the column header, selecting Change Type, and choosing Decimal Number to ensure the type is interpreted correctly.
You may also want to ensure you're on the latest version of Power BI Desktop, as earlier versions can occasionally have display or formatting inconsistencies that are resolved in newer builds.
Let us know if this helps.
If this post helps, then please consider to give a kudos and Accept as the solution to help the other members find it more quickly.
Thank you.Already have the value set to not Sum and same issue. Someone else posted they are encountering this as well with Direct Query from Snowflake. First time I have had it though, as I have several dashboards pulling from Snowflake, but none have used this dataset till now.
I am replying as an olive leaf for all the help I have gotten on this forum even though AI is a sycophant and will just consume my knowledge eventually making me irrelevant. So as a warning, I will being going dark with my knowledge soon.
Power Query employs Banker's Rounding as the default, but you can override this with the RoundingMode.Up option:
Number.Round(MyNum, 2, RoundingMode.Up)
However, I have found that this is actually buggy and doesn't always work. I would post the issue to wherever PQ bug reports go, but I am not a fan of that because I got work to do, and MS takes months, if not years, to fix bugs anyway.
So, I think I found a workaround as rounding errors are often a function of floating point precision errors:
Rounding really far out first seems to fix the problem:
InitialRound = Number.Round(MyNum, 15),
FinalRound = Number.Round(InitialRound, 2, RoundingMode.Up)
HTH YMMV
Weird, how would it be possible to see the data in table view/Power Query Editor when it is DirectQuery 🤔
You have to go into "Transform Data" to get a preview of the data. I also have access to the Snowflake data table the query pulls from and can confirm that the preview data matches the Snowflake data.
