How to Perform Accurate Division in Bold BI
Issue
In Bold BI, when creating expressions to divide two whole numbers, the answer may show up as zero even if the actual result should be a decimal.
Example:
[countofunits]/[units]
In this case, the division of 15 by 150 should result in 0.1 rather than 0
Solution
This is because the columns contain integer values, and integer division rounds down the result. To obtain a precise result, you must convert one of the values to a float or decimal data type.
Sample Data:
Syntax:
[column_name]::data_type/[column_name]::data_type
Explanation:
The syntax “[column_name]::data_type” is used to explicitly change the data type of a column. For instance, using “[column_name]::float” changes the data type to a floating-point number. When you encounter an expression like “[column_name]::float/[column_name]::float” in PostgreSQL, it implies that both the numerator and denominator are converted to floating-point numbers before division. This ensures accurate division results, especially when dealing with fractional values.
Example:
[countofunits]::float/[units]::float
If you already have one of the values as a decimal or float, the results will be in decimal and there is no requirement for a conversion.
Example:
[amount]/[units]
In this case you divide a decimal value by an integer and the result will be a decimal value.
If you need to increase your decimal precision in Bold BI, you must apply the appropriate formatting to the expression or column.
Additional References
