Why parametersied filter do not work on KPI?

I have created a parameterized filter, but it is working fine on table level but do not working on KPI level. And Trying to give as many screen shot as possible to explain the problem

here is the dashboard I have

Just to let you know,

% total_with_A = sum(total_with_A) / sum(total)

Now I want to create a filter that will have three options, ‘All’, ‘A’, ‘B.’
‘All’ will include all the rows,
‘A’ will includes rows where ‘% total_with_A’ < 90%
‘B’ will includes rows where ‘% total_with_A’ >= 90%.

to do that I have created a parameter named as ‘filters’ , passed ‘All’ as static value. then add control, created a list as ‘All’, ‘A’, and ‘B’.

now created a calculated filed named as ‘filters_col’ using calculation below,

ifelse(
${filters} = ‘All’, 1,
${filters} = ‘A’ AND {% total_with_A} < 0.90, 1,
${filters} = ‘B’ AND {% total_with_A} >= 0.90, 1, 0
)

And then used this ‘filters_col’ as filter, and set the value equal to 1, please see the screen shot

now if you look at the filter configuration, aggregation option is set to custom (no other option given, and I think its because of

% total_with_A = sum(total_with_A) / sum(total)

if I do not use sum in that calculation it gives other options such as no aggregation or other .

However, I went ahead and applied the filter. Now if I choose ‘All’ it gives right values, (all the rows) on table and KPI reflect right as well.

If I choose ‘A’ then in the table it includes only rows where ‘%total_with_A’ is < 90%, which is expected, but do not make any change to KPI level, please refere to the screenshot.


As I choose ‘B’ from filter, it again work on the table, keeps rows where ‘% total_with_A’ >= 90% which is right, but in KPI level it says no data. please refer to screenshot.

How can this problem be solved ? Any suggestion will be helpful as I have spent huge amount of time on it without any success and now completely out of my depth.

Hi @sadhanm22,

The way I understand it is that it has to do with the order of evaluation in QuickSight.

In a KPI visual, your aggregation is calculated over your entire dataset since it’s not grouping by any dimension and that value (84.02%) is calculated before your filter is applied.

When you apply your filter and select A, the 2nd condition is satisfied and it shows the aggregated value of 84.02% for your entire dataset.

When you select B, the 3rd condition isn’t satisfied since 84.02% is less than 90% and that’s why it shows no data.

If you want the conditions to be evaluated at the row level, can you calculate filters_col like this instead? You’ll then have the option to select “No aggregation” when you add your filter.

ifelse(
${filters} = ‘All’, 1,
${filters} = ‘A’ AND total_with_A/total < 0.90, 1,
${filters} = ‘B’ AND total_with_A/total >= 0.90, 1, 0
)

You can still calculate % total_with_A to use in your KPI but don’t use it in your filter.

Do you have some hidden columns in your table visual? Without hidden columns, I would expect rows with the same name to be combined when aggregating…

Hi @David_Wong ,
Thank you for replying. You are right I had a hidden column, that has been removed and now name column has only unique values. Also have recalculated the field as you mentioned as

ifelse(
${filters} = ‘All’, 1,
${filters} = ‘A’ AND total_with_A/total < 0.90, 1,
${filters} = ‘B’ AND total_with_A/total >= 0.90, 1, 0
)

But it create new problem. It is not working on table level properly, and also it changing the kpi but do not reflecting the right number. please look at the screen shot. If I select A

from above, If I select A the distinct_count(name) has been changed to 69 from 146 which is expected, but '% total_with_A ’ has been changed to 0, which is not expected, I was expecting it to be around 21%, also table level, last 2 column became 0, which is not right.

now if I select B, distinct_count(name) became 126 which is not expected ( as total number of name is 146, and selecting A gives 69 distinct names, there fore selecting B shall not be 126), also % total_with_A became 100% , which is not right either,


Any thought on it please?

@sadhanm22

At what level do you want the conditions in your ifelse to be evaluated? At the name level or row level? Name level and row level are not the same because the same name appears in multiple rows of your dataset.

Hi @David_Wong ,
Thank you so much.
I want the ifelse to be evaluated on name level, if overall %total_with_A for a name is less than 90% and if I choose B from filters, it shall exclude all rows for a name that’s overall %total_with_A is less than 90%. lets take simple example from the excel sheet below.

I have made a simple dataset just to explain my requirement.

if you look into name col, Both A and B has been appeared 3 times. However
overall %total_with_A for X is more than 90% [(98+90+85)/ (100+100+100)] = 91 % and overall %total_with_A for Y is less than 90% [(90+90+60)/(100+100+100)] = 80%.
Now when I choose B from filter it should only show only 1 distinct_count(name) as KPI, and kPI as %total_with_A shall be 91%, and keep all the rows for X because overall %total_with_A is greater than 90% for X. Please refer to the screen shot .


Now if select A from filter it shall keep all the records for name who’s overall %total is less than 90%, in this case, all the records for Y name, though couple of row has 90% but overall is below 90%. expected result below.

@sadhanm22

If you use sum(total_with_A) / sum(total), your conditions are evaluated at the level which is displayed in your visual. That’s why you get different results in a KPI and in a table.

If you use total_with_A/total, the conditions are evaluated at the row level.

If you want the conditions to always be evaluated at the name level regardless of the visual type, use this instead:

sumOver(total_with_A, [name], PRE_AGG) / sumOver(total, [name], PRE_AGG) 

Thank you so much @David_Wong , it works perfectly fine, exactly the way I wanted.