How to calculate percent of count of UNIQUE values?

I have a dataset with people who can pick multiple symptoms (i.e each person can have 0 to 10 values).

person 1 - symptom A, B
person 2 - symptom B, C, D
person 3 - no symptoms
person 4 - symptom A

etc. E.g. if total UNIQUE count of people is 4 and 2 of them have picked symptom A, then I’d like to see: A = 2/4 = 50% Currently QuickSight is able to calculate shares based on total count of people (not unique count) as one person can have multiple symptoms, so A is 2/6 = 33% (not what I need).

As much as I’ve tried, QuickSight doesn’t enable that??

Hi Eleri! You could create a calculation like this to get A=2/4=50%.

A= countIf(Symptom,Symptom=‘A’)/distinct_count(Person)

I used a sample dataset with this format:

Person Symptom
1 A
1 B
2 B
2 C
2 D
3 None
4 A

Thanks for replying, but I needed a generic formula for all symptoms, not just A.
Btw, I already found a solution:

avg(distinct_count({user_id_string}, [{symptom_name}] ))
/avg(distinctCountOver
(
{user_id_string},
[], PRE_AGG
))

3 Likes