I’m trying to make the below calculation to apply to various categories in the data. These calculated fields are aggregations from the data which contain scores from 0-10 as well as empty cells where a score wasn’t assigned.

when trying to execute this calculation some of the categories do not populate, I believe this may be because some categories have fields such as {111 weighted} that have no scores just null/empty cells which cannot be computed with the other fields that contain a score.

I cant change the null values to 0 as that would indicate 0 was given as a score when it was not. Is there a way of doing this calculation while somehow telling QuickSight to exclude the null values from the calculation?

Hi @trew1 - Welcome to AWS QuickSight community and thanks for posting the question. I understood you can not assign 0 to NULL values for those weighted fields, but when you are doing the final calculation, you have to tag to 0 for those fields are not available or develop a logic, if the fields value is not available then ignore from calculation.

Yes, it makes sense. In Quicksight, you can handle null or empty values in calculations by using conditional expressions. Specifically, you can use the ifelse function to check if a value is null and exclude it from the calculation.

Here’s an example of how you can modify your calculation to exclude null values:

In this modified calculation, the ifelse function is used to check if each field ({115 weighted}, {115b weighted}, etc.) is null. If a field is null, it is replaced with 0. Otherwise, the original value is used. This ensures that null values are treated as 0 in the calculation.

By using this approach, you can perform the calculation while excluding null values and avoiding the misrepresentation of a 0 score when it was not assigned.