0

Lets say I have a table "Table1" with field Field1, "Table2" with Field2. I have another calculated column in "Table1", "CField" that has formula :

CField = ROUND(Table1[Field1] * RELATED(Table2[Field2]); 2)

The formula works well overall, except that some time it doesn't give the right value. For example :

  • Field1 = 50.23
  • Field2 = 0.065

Calculating this in a calculator gives 3.26495. Rounded to 2 decimals should gives 3.26, but in this case, PowerBI gives 3.27.

My best hypothesis here is that the result of Field1 * Field2 is calculated as CURRENCY and thus rounded to 3.265 before being round to 3.27.

My question here is: Is there a way to force Power BI to keep the precision I need and get my desired result of 3.26?

EDIT: 3.27 isn't just a display issue. If I add decimals to the displayed value, it gives 3.27000.

Foxan Ng
  • 6,883
  • 4
  • 34
  • 41
Ken Bourassa
  • 6,363
  • 1
  • 19
  • 28

2 Answers2

6

Somehow I'm able to reproduce this case with a particular setting about data types. You can check if this is the same as your case.

As you said, this has nothing to do with Format (i.e. display issue). The problem lies at Data type. When all three columns are set to Decimal Number, Power BI returns 3.26.

decimal number

However, when Field1 is changed to Fixed decimal number, Power BI returns 3.27.

fixed decimal number

According to Power BI documentation:

Fixed Decimal Number – Has a fixed location for the decimal separator. The decimal separator always has four digits to its right and allows for 19 digits of significance. The largest value it can represent is 922,337,203,685,477.5807 (positive or negative). The Fixed Decimal Number type is useful in cases where rounding might introduce errors.

Therefore, an explanation to this is that the result 3.26495 is rounded to 3.2650 (four decimal places) and then rounded to 3.27 (round by the DAX function).

Conclusion: Changing all Data type to Decimal Number should solve the rounding error problem.

Though it's still unclear to me that why this happens when Field1 has the data type Fixed decimal number, but not CField. Any supplementary comments are welcomed.

Foxan Ng
  • 6,883
  • 4
  • 34
  • 41
  • I kept looking and found that `CURRENCY * REAL = CURRENCY`. Not sure how much it makes sense considering `CURRENCY * CURRENCY = REAL`. Anyway, I ended up fixing my computation by doing `ROUND(Table1[Field1] * CURRENCY(RELATED(Table2[Field2])); 2)`, which isn't ideal, but doesn't affect the result in my situation. I would have prefered to do `REAL(Table1[Field1])`, but such an option doesn't seem to exists. – Ken Bourassa Aug 24 '17 at 14:28
0

Check your column data types. In Power BI, decimal is in reality a floating point so should not be confused with the decimal data type in other database products such as SQL Server.

Brad Larson
  • 170,088
  • 45
  • 397
  • 571
P3Tom
  • 41
  • 4