How to avoid calculations resulting in infinity in Bold BI
When working with data, you may encounter situations where the result of a calculation is infinity. In such cases, you might want to replace the infinity value with 0 or leave it blank. This article will guide you on how to achieve this using an IF expression.
Steps to Replace Infinity with Zero or Blank
In the below mentioned scenario, the requirement is to calculate average for completed attempts. The average returned infinity as the denominator is zero, so we need to modify it accordingly so that the infinity value is replaced.
- Create an expression as follows:
ISComplete = IF(SUM([isComplete])=0, NULL, SUM([isComplete])) - Calculate average using the below expression
SUM([Attempts])/ [ISComplete]
Now the data will be shown as expected, with infinity values replaced by zero or left blank.
Example
Consider the following sample data:
| Student | Attempts | isComplete |
|---|---|---|
| A | 5 | 1 |
| B | 3 | 1 |
| C | 0 | 0 |
Using the expressions mentioned above, the data grid will display the following:
| Student | Attempts | isComplete | AVG Attempts |
|---|---|---|---|
| A | 5 | 1 | 5 |
| B | 3 | 1 | 3 |
| C | 0 | 0 | 0 |
In this example, the infinity value that would have resulted from dividing by zero is replaced with a zero in the AVG Attempts column.
Additional References
