Average counting each instance in a group only once

Question

I'm trying to create a column with an average for each Name, but the formula should only factor in each unique Code instance one time. Here is the data below with expected output on the right.

For name A1, the average is 60, because we are adding 100, 50, and 30 (one instance each of code xx, yy, and zz regardless of how many times the codes appear) and dividing by the 3 unique codes, and for B1 the average is 20 for the same reason ((10 + 25 +25) / 3).

			OUTPUT
Name	Code	Value	Average
A1	xx	100	60
A1	xx	100	60
A1	xx	100	60
A1	xx	100	60
A1	yy	50	60
A1	yy	50	60
A1	yy	50	60
A1	zz	30	60
B1	aa	10	20
B1	aa	10	20
B1	bb	25	20
B1	cc	25	20
B1	cc	25	20

Appreciate the help!

johnt75 · Answer

You can create a measure like
Distinct average =
	AVERAGEX(
		SUMMARIZE( 'Table', 'Table'[Code], 'Table'[Value] ),
		'Table'[Value]
	)

rockstarnomad · Answer

Unfortunately this formula does not work, that generates an average for the whole table, it does not generate an average within each Name

johnt75 · Answer

It should do if you put it in a visual with the Name column

Forum Discussion

Average counting each instance in a group only once

3 Replies

Related content

Counting instances

Average - Distinct Count

Counting nulls

Average and Moving Average

Weekly Average