Create a calculated field operation using two values from one measure filtered by two distinct dimensions?

Question & Answer
, , , , , ,
1

Hello,

Calculated field question:
Given a dimension field (“type”) and a measure field (“count”), where each distinct dimension has an associated “count”, is it possible to create a calculated field operation using two distinct counts filtered by dimension?

ex:
type | count
fruit | 50
apples | 10

given the above, where the measure field is “count” and the dimension field is “type” could a field be created “apples as percentage of fruit” equal to 20% (apples count over fruit count?)

2

Hi @dpatawar, welcome to the QuickSight Community! Amazon QuickSight allows you to create calculated fields that perform operations on your data, including comparisons between different dimension values.

Here’s how you can achieve the desired “apples as percentage of fruit” calculation:

1. Create a Calculated Field

  • Navigate to the analysis where you want to create the calculated field.
  • Click the “Add” button and select “Calculated field”.
  • Name the field (e.g., “Apples Percentage of Fruit”).

2. Write the Calculation

Use the following expression in the calculated field editor:

sumIf(count, type = 'apples') / sumIf(count, type = 'fruit') * 100

  • sumIf(count, type = 'apples') sums the count values where the type is ‘apples’.
  • sumIf(count, type = 'fruit') sums the count values where the type is ‘fruit’.
  • The division calculates the ratio, and multiplication by 100 expresses it as a percentage.

3. Visualize the Result

  • Add the new calculated field to your visual (table, pivot table, etc.).
  • You can then apply formatting to display the results as percentages.

Explanation

The sumIf function is a powerful tool in QuickSight that allows you to conditionally aggregate your data based on specific criteria (in this case, the dimension value). By combining two sumIf functions and a simple division, you can calculate the percentage of apples relative to the total fruit count.

Additional Considerations

  • Zero Division: If your dataset might have scenarios where the count of “fruit” is zero, you might want to add error handling to your calculated field (e.g., using an ifelse statement to handle such cases).
  • Other Calculations: This approach can be easily adapted to calculate percentages for other types of fruits or any other dimension-based comparisons you need.

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!

3

Hi Xclipse,

Thank you for the detailed response

I have attempted the above solution prior to the question & attempted it again this morning, however the result is still NULL

I think what may be happening is QuickSight looks only at rows individually

ex: when type = ‘apples’, type != ‘fruit’ and vice versa, so the equation is one of count_apples / null, null / count_fruit, or null/null

is there a work around for the above behavior?

hopefully I am missing something

5

Hi @dpatawar, maybe create another column with the same values and try applying the filter.

To get a broader feedback with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and please create a new post, so we can look at it from a fresh perspective. (Details on using Arena can be found here - QuickSight Arena).

6

ah, the solution was to create a new visual which does not group by ‘type’, allowing the filter to be applied across all types at once

ex:
type | count | Apples Percentage of Fruit
fruit | 50 | null
apples | 10 | null

will not work but the below will

count | Apples Percentage of Fruit
60 | 20%

thank you for the idea

1 Like