Adding a KPI with a dedicated filter to a table

[engdonna]

I have a field {amount} which is equal to an opportunity monthly value. I use this {amount} * 12 to calculate the {Unique ARR} for either a single opportunity or multiple unique opportunities. I am using this {Unique ARR} field in multi separate KPIs. Each different KPI has a filter applied to just that KPI field. For example: Unique opps - $ARR - Launched - shows me only the amount if the {stagename} = Launched. I want to add these individual KPIs to a table. Is there an easy way to do this?

[RajKavuda]

Hi @engdonna ,

To create a table that includes your individual KPIs with different filters, you can use calculated fields with conditional logic to selectively apply filters for each KPI. You can create calculated fields using ifelse statements that evaluate your {stagename} conditions for each KPI.

For example, you could create a calculated field for your “Launched” KPI using logic like: ifelse({stagename} = ‘Launched’, {Unique ARR}, 0).

When building these calculated fields, make sure to aggregate by the dimensions you want to filter the KPI by.

This approach allows you to build filters directly into the calculated fields themselves using ifelse logic, so each field can have a different “filtered” condition.

For your table visual, you can then add these different calculated KPI fields as measures.

This method is particularly useful when you want to display multiple fields that are all filtered differently, as normal filters would get applied to all fields in the visual.

If you’re working with multiple KPIs that need different filtering conditions, you could also consider using parameters to make your solution more dynamic and flexible.

See related post -

Subject: Need Help with KPI Calculation Ignoring Specific Filters

Hi everyone, I’m facing an issue with a KPI calculation in Amazon Quick Sight and would appreciate any assistance or insights. Background: I have a KPI that sums the field “amount received”. The requirement is that the KPI should respect certain filters and ignore others. I need this solution to be a calculated field because this KPI will be used in other calculations. Simply disabling filters for this visual does not solve my problem. Filters that should be applied: Date Type Filters tha…

Thanks,
Raj Kavuda

AWS Team

[engdonna]

Hi Raj, Thank you for the quick response. I tried a similar query that before adding the filters directly in the KPI. When I create a calculated field with - ifelse({stagename} = ‘Launched’, {Unique ARR}, 0).- or anything similar i get this error - Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.

Additional information: An individual influenced opportunity can be aligned with more than one campaign. So when looking at multiple campaigns, I only want to see each influenced opp once so we don’t count the same opportunity twice.

• The Unique ARR is the {Unique Amount} * 12
• The Unique Amount is sum(min(amount, [opportunityid]))

[RajKavuda]

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)) 

3. 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) 

4. 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

[engdonna]

Hi @RajKavuda Again, thank you for your help.

1. For each KPI visual I do have the filter directly applied to just that KPI.
2. For this query, ifelse(sum({stagename}) = ‘Launched’, sum({Unique ARR}), sum(0)), Quick Suite gives this error - Expression {{argumentName}} for function {{functionName}} has incorrect argument type {{incorrectArgumentType}}. Function syntax expects {{functionSignature}}.
3. for this query - sumOver({Unique ARR}, [{stagename}=‘Launched’], PRE_AGG) - Quick Suite gives this error - For calculation levels PRE_FILTER and PRE_AGG, the operands can’t be aggregated
4. Referencing the link you shared, I tried this query - distinctCountOver(ifelse({stagename} = ‘Launched’, {Unique ARR}, null), PRE_AGG) and got this error - Expression {{argumentName}} for function {{functionName}} has incorrect argument type {{incorrectArgumentType}}. Function syntax expects {{functionSignature}}.

Use Case: We are bringing in a campaign name, Opportunity ID, Opportunity Name, stagename and Amount. The amount is the monthly opportunity value. Based on the campaign(s) chosen in the filter, we want to:

1. view a unique list of opportunities associated with that campaign(s). (This is working in a table).
2. view a distinct count of how many opportunities are listed under that campaign. (This is working as a KPI and in the table). We are calculating this for specific stagename - Using this calculation - distinct_count(ifelse(contains(stagename, ‘Launched’, CASE_INSENSITIVE), opportunityid, NULL))
3. view the amount (value of the opportunity) This is working in the table for all stages using this calculation - sum(min(amount, [opportunityid]))
4. view the total ARR - this is working using this calculation - {Unique Amount} * 12.
5. View the ARR by stage. This is working in the KPI visual because I have a stagename filter applied to the KPI. I can’t seem to find the calculation to create a calculated field so I can add this to the table.

Since each opportunity can be tied to more than one Campaign and what campaigns are viewed depends on the user, I want to try to do this in Quick Suite vs the dataset.

[WLS-Luis]

Hi @engdonna,

Hope everything is well with you! Just checking back in since this thread hasn’t received a response in a while. Were you able to find a solution yourself in the meantime, or are you still running into the same issues you mentioned in your last post? Please help the community by providing an update within the next 3 business days!

Thank you!

[WLS-Luis]

Hi @engdonna,

Since we haven’t received any further updates from you, I’ll treat this inquiry as complete for now. If you have any additional questions, please feel free to create a new post in the community and link this discussion for context.

Thank you!