CODEWITHSUNDEEP
powerbidaxpowerbi-datasource
Ken Bourassa
Ken Bourassa

Reputation: 6512

How to control decimal precision on Power BI's DAX

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 :

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.

Upvotes: 0

Views: 43883

Answers (2)

P3Tom
P3Tom

Reputation: 41

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.

Upvotes: 0

Foxan Ng
Foxan Ng

Reputation: 7151

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.

Upvotes: 7

Related Questions