Average based on distinct values in another column

Hi melz63017. You can get the average to display in the totals row:

To get the Distinct Values measure, I started with a couple of building blocks, shown above as the first two measures.

DistinctCountValue = DISTINCTCOUNT(TableName[Value])

DistinctCountUniqueID = DISTINCTCOUNT(TableName[Unique ID])

The last measure in the table is the Distinct Values measure, which uses both of the building blocks defined above:

Distinct Values = IF(
	HASONEVALUE(TableName[Unique ID]), 
	[DistinctCountValue], 
	SUMX(VALUES(TableName[Unique ID]), [DistinctCountValue]) / [DistinctCountUniqueID]
	)

The HASONEVALUE function at the beginning of the IF statement checks if your current evaluation context has more than one distinct Unique ID value. So on the table rows that show one Unique ID each, there's one value. For a totals row, there would be multiple values (as long as you don't have your table filtered to a single Unique ID anyway).

So whenever we're on a non-totals row, the measure will do the standard calculation for the DistinctCountValue. If we're on a totals row, it will use SUMX to get the sum of each DistinctCountValue when your table is at the UniqueID level, i.e., add up 2 + 1 + 1 to get 4 in this case. Divide that by the number of count of unique IDs, and you get to 1.33.