How to use ! to exclude filtering at a level in quicksight sumover or sum function

Question & Answer
, ,
1

I have a use case where I want to do a waterfall chart showing coverage drop for every segment.
For it it would be very helpful to exclude the denominator to be filtered at segement level.

Formula = (drop in package count for segment)/(overall count of packages at segment)

Segment Drop count Package COunt Ideal formual
A 100 5000 100/(18000)
B 54 6000 54/18000
C 102 7000 102/18000
Total 254 18000 254/18000

While sumover will work, I want to use it to work at different granularities of dates - Day, Month, Quarter with a single formula.

I see that LAC-A functions has a option to exclude a field from aggregations, but it is not working for some reason. Getting an error while writing the calculation.

From documentation: *
*** LAC-A with dynamic-removed group key : sum({sales},** ** [${visualDimensions},!{Country},!{Products}]) It calculates, before visual level aggregation is calculated, the sum of sales, grouping by the fields in the visual’s Group by field well, except country and product.*

2

@Kranthi ,

Sample data and expected output would be helpful . Also what is the error you are getting with the calculation ?

1 Like
3

Error is not showing any error code. I was trying to use the calculated field in edit dataset region.

image
image1732×562 28 KB

4

Hello @Kranthi !

I am going to archive this topic since it has been open for an extended period of time. If you would still like assistance with this, or if you have a new QuickSight question, feel free to post a new topic in the community so you will be at the top of the priority list for a response from one of our QuickSight experts. Thank you!