I'm trying to create a new measure to find the average of the rating from my table, either the whole table or a particular 'id' as selected by a slicer. The issue is, in the original data if a user appears in more than one user group then they have a table row included for each of their groups (eg. harry appears 3 times). I want to show the average of the rating in a visual but can't work out how to calculate it without the duplicated values.
For the below table, I would like to show the average rating for id 1 in the visual as 4.5 ((5+4)/2) instead of 4.75 ((5+5+5+4)/4).
Table
| id | user group | user id | rating |
|---|---|---|---|
| 1 | group 1 | harry | 5 |
| 1 | group 2 | harry | 5 |
| 1 | group 3 | harry | 5 |
| 2 | group 1 | betty | 2 |
| 2 | group 2 | phil | 3 |
| 1 | group 1 | ted | 4 |
Since the specification is to remove duplicate values, but also to consider existing slicers, a possible solution is
Avg Rating =
CALCULATE(
AVERAGEX( SUMMARIZE( T, T[id], T[rating] ), T[rating] ),
ALLSELECTED( T[user id], T[user group], T[rating] ),
VALUES( T[id] )
)