PBI adding fractional decimal numbers

[WTNetAdmin]

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 is one by hand (I may be off on the decimal place count, but the effect is the same):

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

[aaronlebato]

Any update on this?  I'm running into this as well.  Same exact issue.  Data type is MONEY but POWER BI is adding unnecessary fractional decimals.

[WTNetAdmin]

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

[v-sihou-msft]

@WTNetAdmin

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,

[WTNetAdmin]

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

[hohlick]

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

[WTNetAdmin]

I should have added...if I have to apply formatting, with this situation, I end up with rounding errors of + or - a penny in the individual row items, becuase of the fractional artifacts.

[WTNetAdmin]

Thanks.

Yeah, I get that and understand.  I'm a noob at PBI, but an old IT guy.  That is a problem that we've been dealing with for a lot of years.  However, that is primarily a processor problem, not a software problem.

So, a number like 4395.2300000000000000000000000000000000000 (I didn't count the decimal places)  shouldn't be getting rounded up or down or dropping precision.  That problem applies to something like 4395.2345876132456698732475632234 (again, arbitrary and didn't count the decimal places), as in an engineering or astronomical calc.

Summing $4395.23, and then splitting it out again should be extremely clean...in our modern software world.