Subject: Need Help with KPI Calculation Ignoring Specific Filters

Hi everyone,

I’m facing an issue with a KPI calculation in Amazon QuickSight 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 that should be ignored:

  • Procedure
  • Guide Number

Expected Behavior:
For example, if a user applies a filter for Type and a filter for Procedure, the KPI should still show the total sum for the selected Type (i.e., include values regardless of the Procedure filter).

Has anyone encountered a similar scenario or found a workaround to achieve this selective filtering in KPI calculations? Any help or suggestions would be greatly appreciated!

To address the issue with the KPI calculation, you can create a calculated field that uses conditional logic to selectively apply filters. Use the ifelse function to sum the “amount received” based on the desired filters, such as Type and Date, while ignoring others like Procedure. For example, you can create a calculated field with a formula like:

ifelse({Type} = 'desired_type' and {Date} = 'desired_date', {amount_received}, 0)

This calculated field can then be used in your KPI visual to ensure it respects only the specified filters.

Tell me if it’s work, please

Hi @raissa_toledo and welcome to the QuickSight community!
An alternate option you could try;
When building your calculated field, you’ll just want to make sure and aggregate by the dimensions you’d like to filter the KPI.

So for your instance, I would suggest exploring the sumOver calculation, you can setup parameters to manage your filter choices. Then use the parameters to setup your filtering options:

sumOver({amount received}, [${Date}, ${Type}], PRE_AGG)

  • Date and Type listed above are setup in the calculated field as using parameters, that way it takes the filter options into account when running the calculation.

Let us know if you have any additional questions or if one of these solutions works for your case.