Measure to find average of areas based on ID

[cmc099]

Hi there,

I'm having an issue with something that I think should be easy. I have a table that looks something like the following,

Unique row ID	House ID	House area
1	1	100
2	1	100
3	2	200
4	2	200
5	2	200
6	3	50
7	3	50

What I want to do is create a measure that finds the average area of all houses. The house area is always the same for each row with identical house ids. I essentially want to group the houses by their house ids and only keep one instance of its area, then find the average of these. So, for my example table above, I want my measure to output (100+200+50)/3 = 116.67.

I don't want to reduce my table down to just three rows as there are actually way more columns in my actual table that give me different information on the rooms of the house. I have tried calculating using sums and filters but cannot get it to work. I think that within the measure, I'll have to summarize a table that groups all the IDs, then find the average of the area column, but I'm not entirely sure how to do this. Any suggestions? Any help is much appreciated.

[Greg_Deckler]

@cmc099 Try:

Measure = AVERAGEX(DISTINCT('Table'),[House area])

[cmc099]

@Greg_Deckler That still gives me the same issue I've been having unfortunately. The example table I included doesn't include all of the columns that are actually in my table, so every row isn't the exact same even if they share the same House ID, so when I use that measure on my actual table it calculates the mean like, (100+100+200+200+200+50+50)/7, which I don't want, I want to filter by distinct values of the house ID, but I'm not sure how to do this. I've updated the example table with a better view of my actual table now.

[Greg_Deckler · Accepted solution]

@cmc099 Then use:

Measure = AVERAGEX(DISTINCT(SELECTCOLUMNS('Table',"__ID",[House ID],"__Area",[House Area])),[__Area])

[cmc099]

Yes! Thank you! That works perfectly