Forum Discussion
Getting Totals On Each Row
Hello,
I have a basic table visual that has the following columns: Location, event date, presentation title, and attendees. I'm trying to calculate the average number of attendees by location. The numerator is the the total number of attendees for each presenation. The denominator is the total number of events for the location. The requestor wants to see the average on each row. I've tried variations on ALLEXCEPT() to no avail. Here's some sample data. There is an events table, an attendee table, and a presentation table in a star schema.
| Location | Event Date | Presentation Title | Attendees | |
| Location 1 | 4/1/2026 | Build a Widget | 5 | 5 |
| Location 1 | 4/1/2026 | Build a Thingy | 4 | 4 |
| Location 1 | 4/3/2026 | Reinvent the Wheel | 3 | 3 |
| Location 1 | 4/3/2026 | Square Peg In a Round Hole | 2 | 2 |
| Location 2 | 4/10/2026 | Widget Strategy | 6 | 6 |
| Location 2 | 4/10/2026 | Widget Chaos | 3 | 3 |
| Location 2 | 4/12/2026 | Install Thingys | 5 | 5 |
| Counts | 5 events | 7 Presentations | 28 attendees |
Location 1 had 2 events, 4 presentations, and 14 attendees. The average for the location is 7 (14/2)
Location 2 had 3 events, 4 presentations, and 14 attendess. The average is 4.7 (14/3)
The average value of 7 for location 1 needs to be on each row. Similarly, the average of 4.7 for location 2 needs to be on each row.
Thanks in advance!
Please try the measure below:
Avg Attendees per Event = VAR _TotalAttendees = CALCULATE ( SUM ( Events[Attendees] ), REMOVEFILTERS ( Presentations[PresentationTitle] ), REMOVEFILTERS ( Events[EventDate] ) ) VAR _EventCount = CALCULATE ( DISTINCTCOUNT ( Events[EventID] ), REMOVEFILTERS ( Presentations[PresentationTitle] ) ) RETURN DIVIDE ( _TotalAttendees, _EventCount )
8 Replies
Please try the measure below:
Avg Attendees per Event = VAR _TotalAttendees = CALCULATE ( SUM ( Events[Attendees] ), REMOVEFILTERS ( Presentations[PresentationTitle] ), REMOVEFILTERS ( Events[EventDate] ) ) VAR _EventCount = CALCULATE ( DISTINCTCOUNT ( Events[EventID] ), REMOVEFILTERS ( Presentations[PresentationTitle] ) ) RETURN DIVIDE ( _TotalAttendees, _EventCount )Hi,
Write these calculated column formulas
Event count = =CALCULATE(DISTINCTCOUNT(Data[Event Date]),FILTER(Data,Data[Location]=EARLIER(Data[Location])))
Attendee count = =CALCULATE(SUM(Data[Attendees]),FILTER(Data,Data[Location]=EARLIER(Data[Location])))
Location average = =DIVIDE(Data[Attendee count],Data[Event count])
Also, there are only 2 events in Location 2.
Hope this helps.
Ashish,
Thank you for your assistance. I am running into an error. I only have attendee names and SUM and SUMX throw errors.
I do not understand. As you can see in my screenshot, my formulas are working.
Hi tom-lenzmeier -Can you pleae try the below measure:
Avg Attendees by Location =
VAR CurrentLocation = MAX('Table'[Location])
VAR TotalAttendees =CALCULATE(SUM('Table'[Attendees]),FILTER(ALL('Table'),'Table'[Location] = CurrentLocation))
VAR DistinctEvents =CALCULATE(DISTINCTCOUNT('Table'[EventID]),FILTER(ALL('Table'),'Table'[Location] = CurrentLocation))
RETURNDIVIDE(TotalAttendees, DistinctEvents)could you pls provide some sample data ? not the table view , the data from three tables and how they connect to each other.
Hi tom-lenzmeier,
I wanted to check if you had the opportunity to review the information provided by cengizhanarslan. Please feel free to contact us if you have any further questions.
Thank you.
