PBI adding fractional decimal numbers

Question

PBI seems to add extended decimal numbers.&nbsp; Sample below.&nbsp;I am pulling in data from SQL Server 2K8.&nbsp; They are Decimal columns.&nbsp;In the query they are cast as Money (a view, actually).&nbsp; I've tried rounding with truncation, and various forms of convert &amp; cast, including recursive (or Cast back), and a few other weird things that shouldn't be.&nbsp; I even ran SQL_VARIANT_PROPERTY to make sure the view was sending what I thought it should...&nbsp;&nbsp;However, if the number has no cents, it appears to be fine in PBI.&nbsp; If it has cents, it starts adding fractional decimal digits...but they add up correctly in the totals.&nbsp; It just seems to arbitraility divide up the extended decimals beyond the 2 digits in its summing.&nbsp;In all other pulls - SQL Mngmt Studio, Excel, SSRS, even an export from PBI to CSV from the "broken" table, the numbers look normal, with 2 cents digits.&nbsp; Even when I look at the query data in PBI.&nbsp;I'm at a loss...&nbsp; Any thoughts?&nbsp;BobCan't paste a snippet so, here is one by hand (I may be off on the decimal place count, but the effect is the same):&nbsp;&nbsp;Acct&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Min&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Medical&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ER01&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $190&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $192.930000000000000001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $192.93000000000000000102&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $150&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $150&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $311.819999999999999999&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $311.81999999999999999903&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $0&nbsp;Total&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $440&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $300&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $504.75&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $504.75

wtnetadmin · Answer

PBI seems to add extended decimal numbers.&nbsp; Sample below.&nbsp;I am pulling in data from SQL Server 2K8.&nbsp; They are Decimal columns.&nbsp;In the query they are cast as Money (a view, actually).&nbsp; I've tried rounding with truncation, and various forms of convert &amp; cast, including recursive (or Cast back), and a few other weird things that shouldn't be.&nbsp; I even ran SQL_VARIANT_PROPERTY to make sure the view was sending what I thought it should...&nbsp;&nbsp;However, if the number has no cents, it appears to be fine in PBI.&nbsp; If it has cents, it starts adding fractional decimal digits...but they add up correctly in the totals.&nbsp; It just seems to arbitraility divide up the extended decimals beyond the 2 digits in its summing.&nbsp;In all other pulls - SQL Mngmt Studio, Excel, SSRS, even an export from PBI to CSV from the "broken" table, the numbers look normal, with 2 cents digits.&nbsp; Even when I look at the query data in PBI.&nbsp;I'm at a loss...&nbsp; Any thoughts?&nbsp;Bob&nbsp;Can't paste a snippet so, here it is by hand:&nbsp;Acct&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Min&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Medical&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ER01&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $190&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $192.930000000000000001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $192.93000000000000000102&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $150&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$150&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$311.819999999999999999&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $311.81999999999999999903&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$0&nbsp;Total&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $440&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $300&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $504.75&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$504.75

v-sihou-msft · Answer

WTNetAdmin
&nbsp;
Just to confirm that if the original data in SQL or other pulls (e.g. Excel below) have the fractional decimal digits? They may only show the rounded numbers to you.
&nbsp;

&nbsp;
If yes, you can select the column and change the number of decimal places as below.
&nbsp;

Regards,

wtnetadmin · Answer

Thanks.&nbsp;I'm a noob in PBI, so a bit struggling with some of the terminology still, but...&nbsp;Therein lies the problem.&nbsp; It is entered/stored as "money", as in digits&lt;decimal&gt;2 decimal places (cents), but when it gets into the charts/graphs it starts breaking it into the finer decimals...which messes with the graphs and pop-ups.&nbsp; My CFO does not want to see $4,563.238999999991 when he hovers over a bar.&nbsp;My guess would be, at this early stage, that when it autosums, trying to be helpful, it doesn't extract the figures into individuals again well.&nbsp;Yet, as I was trying to say (without writing a book), if I export the data from the directly from the chart to a CSV, I get the original data...with just 2 cents.&nbsp;The problem appears to be specifically in the charts themselves...overthinking their display plan.&nbsp;Bob

hohlick · Answer

Hi&nbsp;WTNetAdminAs&nbsp;v-sihou-msft&nbsp;mentioned above, these could be solved by added fata format with "Modelling" tab above, where you can set 2 digits after delimeter format. I think that problem itself lies in Double-precision floating-point format

wtnetadmin · Answer

Thanks.&nbsp;Yeah, I get that and understand.&nbsp; I'm a noob at PBI, but an old IT guy.&nbsp; That is a problem that we've been dealing with for a lot of years.&nbsp; However, that is primarily a processor problem, not a software problem.&nbsp;So, a number like 4395.2300000000000000000000000000000000000 (I didn't count&nbsp;the decimal places)&nbsp; shouldn't be getting rounded up or down or dropping precision.&nbsp; That problem applies to something like 4395.2345876132456698732475632234 (again, arbitrary and didn't count the decimal places), as in an engineering or astronomical&nbsp;calc.&nbsp;Summing $4395.23, and then splitting it out again&nbsp;should be extremely clean...in our modern software world.

Forum Discussion

PBI adding fractional decimal numbers

7 Replies

Related content

Convert Decimals to Custom Fractions

show numbers as fraction

Decimal Precision

Numeric sorting of decimal numbers in powerbi

Problem calculating with decimal numbers