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

Uploaded Jan 31, 202410.17 KB

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?
Member ID	ABC	Member Name	Member 1	Weight	0%	Registered for this current show?	Not Registered
Member ID	DEF	Member Name	Member 2	Weight	0%	Registered for this current show?	Not Registered
Member ID	JKL	Member Name	Member 3	Weight	20%	Registered for this current show?	Not Registered
Member ID	JKL	Member Name	Member 3	Weight	0%	Registered for this current show?	Registered
Member ID	MNO	Member Name	Member 4	Weight	100%	Registered for this current show?	Not Registered
Member ID	MNO	Member Name	Member 4	Weight	0%	Registered for this current show?	Registered
Member ID	STU	Member Name	Member 5	Weight	80%	Registered for this current show?	Not Registered
Member ID	STU	Member Name	Member 5	Weight	0%	Registered for this current show?	Registered
Member ID	QRS	Member Name	Member 6	Weight	60%	Registered for this current show?	Not Registered
Member ID	QRS	Member Name	Member 6	Weight	0%	Registered for this current show?	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!!