Identify percent of people attending current event

deona720

I am currently trying to gauge how many consistent event attendees are attending our upcoming show. For context, we have 2 events every year and every registration for an event is on a separate record which I have categorized by year between 2021-2024. I am trying to roll this up into a table that gives us a percentage of how many previous events they have attended and if they are attending 2024 or not. If they have attended 60% (3/5) or higher of our previous events, and have not registered for this one - we want to reach out and find out why.

I have the data that I need but I am not sure how to group my table my member ID.

This is what my raw data looks like and how I want my table to look.

DOMO Example.xlsx

However, I can't get the member IDs to group when I try to incorporate 2024 registrations. Normally I would add a distinct member id count beast mode and it groups them but when I add this beast mode:

CASE when Year like '%2024%' then 'Registered' else 'Not Registered' END

It creates a table like this:

Member ID	Member Name	Weight	Registered for this current show?
ABC	Member 1	0%	Not Registered
DEF	Member 2	0%	Not Registered
JKL	Member 3	20%	Not Registered
JKL	Member 3	0%	Registered
MNO	Member 4	100%	Not Registered
MNO	Member 4	0%	Registered
STU	Member 5	80%	Not Registered
STU	Member 5	0%	Registered
QRS	Member 6	60%	Not Registered
QRS	Member 6	0%	Registered

How do I make it stop pulling up duplicate records to say they are registered for 2024?

DavidChurchman

You need to give your "registered" vs. "not registered" column a way to aggregate, otherwise it doesn't know how to collapse the different values. I think this should work (it's basically just wrapping your beastmode in a SUM and another CASE statement)

case
when sum(case when Year like '%2024%' then 1 else 0 end) > 0
then 'Registered'
Else 'Not Registered'
END

DavidChurchman

You need to give your "registered" vs. "not registered" column a way to aggregate, otherwise it doesn't know how to collapse the different values. I think this should work (it's basically just wrapping your beastmode in a SUM and another CASE statement)

case
when sum(case when Year like '%2024%' then 1 else 0 end) > 0
then 'Registered'
Else 'Not Registered'
END

deona720

@DavidChurchman That worked perfectly, thank you so much!!