Sum on measure table not working correctly

[stevedata]

Hi All,

hoping you can help me with an issue i am having summing up % values for data (multiple rows belonging to a category) when creating a table.

  example source data

Category	Activity	Total Invites	Attendees
Events	Event1	10	5
Events	Event2	30	15
Events	Event3	20	15

In PowerBI Desktop I have added a new column to calculate rate of attendance - formula used is below

data				new calculated column = [Total Invites]/[Attendees]
category	activity	Total Invites	Attendees	Attendance Rate
Events	Event1	10	5	50%
Events	Event2	30	15	50%
Events	Event3	20	15	75%

when I create a table in PowerBI, based on the category Events, the % it shows is not correct - 

Category	Total Invites	Attendees	Attendance Rate
Events	60	35	175%

how do I get to show this rate as 58%?

Regards

[mahoneypat · Accepted solution]

You need to create a measure to return the average of your Attendance column, or just change the aggregation to Average in your visual (if you just added the Attendance column directly).

Pat

[stevedata]

@mahoneypat  thanks for your response - I have just realsied that with the example numbers I provided this works and average is 58% but with my actual data I am using the issue is still not resolved - see below for when i select 'dont summise', 'sum, and 'average' options.

Don’t summise				new calculated column = [Total Invites]/[Attendees]
Category	activity	Total Invites	Attendees	Attendance Rate
Events	event1	48	23	48%
Events	event2	20	10	50%
Events	event3	73	87	119%

Sum
Category	Total Invites	Attendees	Attendance Rate
Events	141	120	217%

Average
Category	Total Invites	Attendees	Attendance Rate
Events	141	120	72.4%

72.4 is indeed the average accross all 3 events but instead of this I want the 85% number that shows the rate between total attendance vs total invites i.e. 120/141 = 85%

[stevedata]

@mahoneypat ah just realsied in  my 'event 3' data theres an issue there where i have more attendees than invotes so your solution was correct, thanks again!