Finding average of number of items across categories

[tarinaama]

I have a data set that has information on user name and the event that they attended. Some users attended multiple events so there are multiple objects with same username. I want to calculate what the average number of event attendees are that works with filters. This is syntax that I was using previously, but it was not changing as I was changing the filters (for example each event name is tied to a larger event, so when filtering on the parent event, the number was not changing). Average Attendees per Event = DIVIDE(DISTINCTCOUNT(ATTENDEES[USERNAME]), DISTINCTCOUNT(ATTENDEES[EVENT_NAME]),0). How can I create a function that will execute this and work with filters

[techies]

Hi @tarinaama please share a sample datasets to test DAX.

[v-mdharahman]

Hi @tarinaama,

Thanks for reaching out to the Microsoft fabric community forum and for clarifying that both USERNAME and EVENT_NAME are in the same tablle, which definitely changes things.

The previous suggestion would work if your data model had a separate EVENT dimension table and a relationship to your attendance data. But since you're using a single flat table, that DAX won't give the expected results as it evaluates the same attendee count across all rows.

Here’s the corrected version that should work with your current setup:

Avg. Attendee count :=
AVERAGEX(
VALUES(YourTable[EVENT_NAME]),
CALCULATE(DISTINCTCOUNT(YourTable[USERNAME]))
)

This will count the number of distinct attendees for each event, then it averages those event-level counts. And it responds correctly to filters like category or parent events.

I would also take a moment to thank @Jihwan_Kim, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.

[tarinaama]

Hi Hamad, 

Really appreciate the help, however this formula still does not work for my PowerBI. When inputting, the value I am getting is 1 and that is incorrect. Might be because the event names are strings and usernames are emails? Not too sure, still trying to troubleshoot this problem, any other avenues of exploration to try would be grately appreciated!

[Jihwan_Kim]

Hi, I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file whether it suits your requirement.

Attendee count: = 
COUNTROWS(DISTINCT(VALUES(ATTENDEES[USERNAME])))

Avg. Attendee count: = 
AVERAGEX(VALUES(EVENT[EVENT_NAME]), [Attendee count:] )

[tarinaama]

Hi! Really appreciate the help, unfortunately this does not work, when implementing these formulas in my dashboard, I am getting the same values for the total count and the average count. The username information and event information are within the same table within the PowerBI dashboard I am working on as well, if that changes anything!