Forum Discussion
PBI adding fractional decimal numbers
PBI seems to add extended decimal numbers. Sample below.
I am pulling in data from SQL Server 2K8. They are Decimal columns.
In the query they are cast as Money (a view, actually). I've tried rounding with truncation, and various forms of convert & cast, including recursive (or Cast back), and a few other weird things that shouldn't be. I even ran SQL_VARIANT_PROPERTY to make sure the view was sending what I thought it should...
However, if the number has no cents, it appears to be fine in PBI. If it has cents, it starts adding fractional decimal digits...but they add up correctly in the totals. It just seems to arbitraility divide up the extended decimals beyond the 2 digits in its summing.
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. Even when I look at the query data in PBI.
I'm at a loss... Any thoughts?
Bob
Can't paste a snippet so, here it is by hand:
Acct Min Medical EE ER
01 $190 $50 $192.930000000000000001 $192.930000000000000001
02 $150 $150 $311.819999999999999999 $311.819999999999999999
03 $100 $100 $0 $0
Total $440 $300 $504.75 $504.75
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.
If yes, you can select the column and change the number of decimal places as below.
Regards,
Thanks.
I'm a noob in PBI, so a bit struggling with some of the terminology still, but...
Therein lies the problem. It is entered/stored as "money", as in digits<decimal>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. My CFO does not want to see $4,563.238999999991 when he hovers over a bar.
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.
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.
The problem appears to be specifically in the charts themselves...overthinking their display plan.
Bob
Hi WTNetAdmin
As v-sihou-msft 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
