Average users per group

[pgardella]

Good morning!  I am just starting to use PowerBI and DAX, and have been frustrated by what seems like it should be an easy thing.

Given a table like this:

Group_Name	Group_ID	User_ID
Group 1	1	100
Group 1	1	200
Group 1	1	300
Group 2	2	100

I want to calculate the average number of people per group. In this case "2" ((3 + 1)/2). I've tried setting up a second table based using SUMMARIZE, so it ends up like:

Group_ID	UserCount
1	3
2	1

And then tried to calculate the AVERAGE of that new table.  But my SUMMARIZE never turns out right. 

SUMMARIZE(group_user_links,group_user_links[group_id], "UserCount", DISTINCTCOUNT(group_user_links[user_id])) or

SUMMARIZE(group_user_links,group_user_links[group_id], "UserCount", COUNTX(group_user_links, group_user_links[user_id])) end up way too high.

Is that the best way to go about it? Or is there a better way?

Patrick+

[Sean · Accepted solution]

@pgardella This should work!

User Per Group =
DIVIDE (
    CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Group_ID] ) ),
    DISTINCTCOUNT ( 'Table'[Group_Name] ),
    0
)

[pgardella]

Well, that is one way I hadn't even considered!  Thank you!