Adding a KPI with a dedicated filter to a table

Q&A
, ,
4

Hi @engdonna ,

The error you’re encountering is due to mixing aggregated and non-aggregated fields in your calculated field.

When using ifelse statements in Amazon QuickSight, you cannot combine aggregated fields (like your {Unique ARR}) with non-aggregated fields (like {stagename}) in the same calculation.

To solve this issue, you have several options:

  1. Instead of creating a calculated field with the filter condition, apply the filter directly to your visual for each KPI.

    For example, create a filter on your target visual for {stagename} = ‘Launched’ and then use your {Unique ARR} calculation without the conditional logic.

    See similar post - How to filter a calculated field

  2. Use aggregation functions consistently throughout your calculation. See similar post - Ifelse with calculated fields

    For example:

ifelse(sum({stagename}) = 'Launched', sum({Unique ARR}), sum(0))

  1. Consider using level-aware calculations (LAC) with window functions.

    This approach allows you to make aggregated values available at the row level:

sumOver({Unique ARR}, [{stagename}='Launched'], PRE_AGG)

  1. For your specific case with multiple campaigns and unique opportunities, you might need to use distinctCountOver with ifelse inside it.

    This would help ensure each opportunity is counted only once across campaigns.

If these approaches don’t work for your specific data structure, you might need to prepare your data at the dataset level using SQL before bringing it into QuickSight.

Thanks,

Raj Kavuda