Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Decimal accuracy in report view versus data view

Problem with very small amounts being added to my decimal values (i.e. .000000000000050). This is happening with my SQL data but I can repro it without an external datasource. Hopefully someone will know a secret I'm missing. Here goes:

  • Open a blank Power BI report, click Enter Data from the Home tab. Under Column 1, Row 1; enter A and hit Tab. Column 2 appears; enter 754.25 and hit Tab. Column 3 appears; enter 739.16 and hit Tab. Column 4 appears; enter 15.09. On the next several rows, continue entering the following:
    • Row 2; B, 975.32, 955.81, 19.51
    • Row 3; C, 192787.73, 177609.54, 0
    • Row 4; D, 905.17, 905.17, 0
  • Click to open Data view. You have a Table with 4 rows.
  • Click Report view, add a Table visualization, and add Column1, Column2, Column3, and Column4. Right click the Table name and select New column. Define it as: Column = [Column2] - [Column3] - [Column4]. You will see three of the rows show Column as zero. Add Filter on Column as is not 0 and hit Apply. Hmmmm...only one row disappeared. Why?
  • Click the Column field, click the Modeling tab, and change the Formatting Decimal Places to 15 so you can see what's what. Aha! Now you see the deception: Row 1 (A) is 0.000000000000032 and row 2 (B) is 0.000000000000103. Also notice row 3 (C) is 15178.190000000002000. Set the Decimal Places for Column2, Column3, and Column4 to 15. You'll see many of them with the same problem.
  • Click Data view and notice all the data appears correct here, except for Column.

You can guess how this is wreaking havoc in a complex report that's based on whether or not Column is zero or not.

I can change the definition for Column to: Column = Round([Column2] - [Column3] - [Column4], 6) and that resolves it for this filter, but there are many other tests that are not as straight forward.

This seams similar to this post: Power Query decimal precision problem - does not get to 0 when negative values equal positive values , but the solution there seems to be a band-aid to a bigger problem. Or maybe I don't know how to efficiently apply that solution to my situation.

I'd like to know if there's a quicker, more fundamental solution, before going through all of my calculations to add ROUND() all over, while not being entirely sure it will be effective in every case.

 

 

  • Hi Anonymous ,

    It's difficult to explain the decimal precision in power bi desktop. Just like your test, the data we entered before will change after we change the number of decimal places in some moments. Perhaps the issue you have previously mentioned is a favorable way but in my opinion, use Round() function may be a better or even the best way to solve this issue.

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

     

1 Reply

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support

    Hi Anonymous ,

    It's difficult to explain the decimal precision in power bi desktop. Just like your test, the data we entered before will change after we change the number of decimal places in some moments. Perhaps the issue you have previously mentioned is a favorable way but in my opinion, use Round() function may be a better or even the best way to solve this issue.

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.