Ignore filter when exclude values

Question & Answer
, , , ,
1

I have a requirement that I want to have total calculate all values like below
which is 10.5

image
image1414×717 22.5 KB

But I need to exclude “Others” from the table but not change the value of total.
If I’m using PRE_FILTER I can’t filter by “Status” in my filter
I need to filter by everything but just want to Hide “Others” from my table without changing the “Total Value” but this “Total Value” will change based on analysis filter (status).
Below if I hide “Others” from table, it’s showing 9.5 which is wrong
image

if in Power BI / Tableau we have “ALL” or “EXCLUDE” syntax, if it’s possible to achieve this in Quicksight?

Below is the data sample
image

and some calculations I used for reference
value avg = avg(Value,[Category])
Total value = sumOver(sum({value avg}))

2

Hi @consultantcon - I do not think, it is possible in QS now. It may be a feature request. By saying that, let’s hear from other experts.

Hi @David_Wong @duncan @DylanM @sagmukhe @Biswajit_1993 - Please provide your advise on this.

Regards - Sanjeeb

1 Like
3

hi @consultantcon, I will need to check and do it from sides then get back to you.

Thanks for raising this question.

Thanks & Regards
Biswajit Dash

1 Like
4

@consultantcon - The total in the table visual will only show the total value of the categories shown in the visual. You can calculate the total you want in QuickSight using level-aware calculations. These are functions like sumOver, minOver, maxOver etc wherein you can specify the partition at which to calculate and whether to do it ahead of filtering or aggregation. You can find an example on the QuickSight democentral site.

2 Likes
5

Hi @Kellie_Burton
Yes I used the LAC you can see as in my post. But I just want to hide Others from my visuals without changing the number

6

Hello @consultantcon, my apologies for the delayed response. If you are still struggling with this issue, my thought is to update both of your fields to look like this:

value avg = avgOver({Value}, [{Category}], PRE_FILTER)
Total value = sumOver({value avg}, [], PRE_FILTER)

And if PRE_FILTER does not work how you are expecting, you can also try using PRE_AGG instead.

1 Like
7

Hi @DylanM

Thanks for taking your time for checking this. I tried your method but it’s still not as expected, when I filter Others the total changed.
you can refer my screenshot below

image
image1368×650 66.9 KB

image
image1368×707 69.6 KB

and actually the total is different not sure why, it’s should following “avg Value” total.
the expected total value will be still 10.5 even if the second table is filtered out without “Others”

as I understand from the AWS team after had a call with them that this feature is still not there.
Not sure who can put as feature request for this.

Thanks

8

Hello @consultantcon, yeah I think it is having an issue with running the average value on each grouping and then aggregating the sum on each of those values. You may have some luck if you add the average for each category in your dataset by setting those values in SQL, then aggregated the total in a calculated field. That is your best bet to get the desired solution for now.

Thank you for your feedback. I will mark this topic as a feature request and archive this question.