How to resolve issue with dividing two aggregated expression in Bold BI

Published:

In some cases, you may face issues with division. It is depends upon different cases. This article explains the possibility of two different cases and how to resolve the issue.

First Case

The Expression Designer may not provide the expected result when dividing multiple aggregated statements.

Problem

Consider you’re dividing the aggregated values as shown below. As a result, you may not get the expected results.

(SUM(IF([Status]='Delivered',1,0)))/(SUM(IF(([Status]='Delivered') OR ([Status]='Processing'),1,0)))

Solution

To resolve this issue, you need to cast the columns internally to decimal before dividing the aggregated expressions.

Updated Expression

Apply casting to the aggregated expressions before dividing:

CAST((SUM(IF([resultado]='SUCESSO',1,0))) AS DECIMAL(38,8))/CAST((SUM(IF(([resultado]='SUCESSO') OR ([resultado]='INSUCESSO'),1,0))) AS DECIMAL(38,8))

Note: This expression works for SQL Server, MySQL, Postgre SQL and other connectors which supports decimal datatype

By applying the proper casting, you can ensure that the division operation in the Expression Designer provides accurate results.

You can apply Percentage for better visualization by following the below steps.

Select Format option for the Column as shown in following image
The ‘Measure formatting Window’ will be open. Select the type as Percentage.

Refer the below image after applied Percentage.

Second Case

When dividing two calculated columns or decimal values, you may encounter an error message stating “Specified case is not valid.” This issue occurs when the output of the division has more decimal values than the system can handle. To resolve this issue, you need to round the value to a certain number of decimal places.

Rounding Decimal Values

To round the value, you can use the ROUND() function in your calculated column expression. Here’s an example of how to round the result of a division to 8 decimal places:

ROUND(Column1 / Column2, 8)

Replace Column1 and Column2 with the appropriate column names in your data source, and replace 8 or the desired number of decimal places.

By default, in Bold BI widgets, the value will be rounded to 2 decimal places. You can adjust this setting according to your needs.