Forum Discussion
Sum on measure table not working correctly
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
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
3 Replies
- mahoneypat
Microsoft Employee
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
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%
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!
