SUM attendees with duplicate values

[marchn]

Hello all

I have been so grateful for the support I've received from this forum since I embarkd on my Power BI journey a month or two ago. I've learned so much, but still SO much to learn. I would be very grateful if someone could help me. 

I have duplicate data in numerous sources in my query, which is necessary because I have records from a Microsoft list where users can select multiple values in a number of different fields. Therefore, I have no option but to expand the data. And this has worked well until I hit a brick wall with this issue. 

I have a source which allows users to record details of engagement activity, the organisation(s) involved, the project(s) discussed and the number of attendees at a given engagement. As per the dummy data below:

ID	Title	Format	Organisations	Project(s)	Attendees
1	Superman	Email	Mickey Mouse Club House Donald Duck inc	Comedy Romance	4
2	Wonder Woman	Webinar	Tom Ltd Jerry Ltd Tweetie Pie and Co	Horror Comedy	2
3	Batman	Email	Daffy Duck Pies	Documentary	2
4	Iron Man	Phonecall	Goofy HQ	Horror Thriller	1
5	Wolverine	Email	Pluto Dog House Popeye Sailors and Son	Horror Thriller	5
6	The Hulk	Webinar	Monsters Inc Mike R Scare Factory	Comedy Romance Thriller	18
				TOTAL	32

However, when I expanded the data in the query, I had the issue of the attendee numbers repeating. No problem, I did "grouped by", then calculated the number of repeating IDs and created a new column to show Average number of attendees per row = Attendees / number of occurences of each ID - as per the below. This solves the problem of the TOTAL numer of attendees. 

ID	Title	Format	Organisations	Project(s)	Attendees	Average Attendees
1	Superman	Email	Mickey Mouse Club House	Comedy	4	1
1	Superman	Email	Mickey Mouse Club House	Romance	4	1
1	Superman	Email	Donald Duck inc	Comedy	4	1
1	Superman	Email	Donald Duck inc	Romance	4	1
2	Wonder Woman	Webinar	Tom Ltd	Horror	2	0.333333333
2	Wonder Woman	Webinar	Tom Ltd	Comedy	2	0.333333333
2	Wonder Woman	Webinar	Jerry Ltd	Horror	2	0.333333333
2	Wonder Woman	Webinar	Jerry Ltd	Comedy	2	0.333333333
2	Wonder Woman	Webinar	Tweetie Pie and Co	Horror	2	0.333333333
2	Wonder Woman	Webinar	Tweetie Pie and Co	Comedy	2	0.333333333
3	Batman	Email	Daffy Duck Pies	Documentary	2	2
4	Iron Man	Phonecall	Goofy HQ	Horror	1	0.5
4	Iron Man	Phonecall	Goofy HQ	Thriller	1	0.5
5	Wolverine	Email	Pluto Dog House	Horror	5	1.25
5	Wolverine	Email	Pluto Dog House	Thriller	5	1.25
5	Wolverine	Email	Popeye Sailors and Son	Horror	5	1.25
5	Wolverine	Email	Popeye Sailors and Son	Thriller	5	1.25
6	The Hulk	Webinar	Monsters Inc	Comedy	18	3
6	The Hulk	Webinar	Monsters Inc	Romance	18	3
6	The Hulk	Webinar	Monsters Inc	Thriller	18	3
6	The Hulk	Webinar	Mike R Scare Factory	Comedy	18	3
6	The Hulk	Webinar	Mike R Scare Factory	Romance	18	3
6	The Hulk	Webinar	Mike R Scare Factory	Thriller	18	3
				TOTAL	160	32

However, I'm now at the stage where I need to create visuals based on total number of attendees involved in a given project. This means that the Total is incorrect, but so is the average. As per the below where I have selected just Comedy:

ID	Title	Format	Organisations	Project(s)	Attendees	Average Attendees
1	Superman	Email	Mickey Mouse Club House	Comedy	4	1
1	Superman	Email	Donald Duck inc	Comedy	4	1
2	Wonder Woman	Webinar	Tom Ltd	Comedy	2	0.333333333
2	Wonder Woman	Webinar	Jerry Ltd	Comedy	2	0.333333333
2	Wonder Woman	Webinar	Tweetie Pie and Co	Comedy	2	0.333333333
6	The Hulk	Webinar	Monsters Inc	Comedy	18	3
6	The Hulk	Webinar	Mike R Scare Factory	Comedy	18	3
				TOTAL	50	9

The actual number of attendees involved in the Comedy project is 24 - I.e the sum of attendees based on distinct IDs. If I take ID 1 for instance - there were 4 attendees involved in comedy for ID 1, because all 4 attendees did both. However, the average would assume it was only 2. The total would assume it was 16. Can anyone help me to resolve this? I would be very grateful. I've tried lots of things with SUMX to sum distinct values, but everything I've tried doesn't work. 

[audreygerred]

You're very welcome! Happy to help!

[marchn]

Hello audreygerred - thank you so much! That works perfectly! Really grateful. Natasha

[audreygerred · Accepted solution]

Hello - instead of creating the average column, create the below measure:

Total Attendees = SUMX(DISTINCT(Table[ID]),FIRSTNONBLANK(Table[Attendees], 0))

I get the below:

Hope this helps! Have a great day!